I have a function built that will get me X percentile from my history table for
last X days. It works fine as long as I hard code the certain values in where
they go... what I really need to know is how can I make dynamic sql still do a
select into a variable? simply replacing the '31' below with 'duration' does
not work in here because it is not a proper place for a variable
substitution....
DELIMITER $$
--
-- Definition for function firescope.getPercentile
--
DROP FUNCTION IF EXISTS firescope.getPercentile$$
CREATE FUNCTION getPercentile(iid BIGINT(20), duration INT, percentile INT)
RETURNS TEXT CHARSET LATIN1
BEGIN
DECLARE cnt, pct BIGINT(20);
DECLARE temp TEXT;
SELECT
COUNT(*), AVG(value)
INTO
cnt
FROM
history
WHERE
itemid = iid
AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)); /* this 31
should be duration from the in list*/
SET @calc = 100 / (100 - percentile);
SET @yarp = cnt / @calc;
SELECT
value
INTO
temp
FROM
history
WHERE
itemid = iid
AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)) /* this 31
should be duration from the in list*/
ORDER BY
value DESC
LIMIT
0, 1; /*this 0 should be @yarp*/
RETURN temp;
END
$$
DELIMITER ;
