I'm rewriting my diabetic monitoring app and I want to give myself daily, weekly, monthly and period averages. I think this will work but I'd just like someone to confirm my logic.


DAILY

SELECT
CAST(_TimeStamp AS DATE) AS Point,
AVG(_mmolPL) AS Results
FROM BloodTests
WHERE _TimeStamp >=  CURRENT_DATE - INTERVAL '90' DAY
GROUP BY Point
ORDER BY Point ASC


WEEKLY

SELECT
(EXTRACT(YEAR FROM _TimeStamp)*100)+ EXTRACT(WEEK FROM _TimeStamp) AS Point,
AVG(_mmolPL) AS Results
FROM BloodTests
WHERE _TimeStamp >=  CURRENT_DATE - INTERVAL '90' DAY
GROUP BY Point
ORDER BY Point ASC

MONTHLY

SELECT
(EXTRACT(YEAR FROM _TimeStamp)*100)+ EXTRACT(MONTH FROM _TimeStamp) AS Point,
AVG(_mmolPL) AS Results
FROM BloodTests
WHERE _TimeStamp >=  CURRENT_DATE - INTERVAL '90' DAY
GROUP BY Point
ORDER BY Point ASC


PERIOD

SELECT
AVG(_mmolPL) AS Results
FROM BloodTests
WHERE _TimeStamp >=  CURRENT_DATE - INTERVAL '90' DAY


Roy Lambert

Roy,

I think it would help if you add the table definition.

What type of averages are you trying to calculate?

Are you trying to calculate SMA's (simple moving averages) of _mmolPL? So generally speaking, if you wanted a monthly SMA for _mmolPL on Dec 30th you would average the _mmolPL for all the days between Dec 1st and Dec 30th? (30 days in a month) Then Jan 1st monthly SMA would be the _mmolPL values from Dec 2 to Jan 1st, and Jan 2nd would be the average _mmolPL from Dec 3rd to Jan 3rd etc?

If so, then you can't use Current_Date() because this will give you one average for each period as of the current date. Or is that what you are trying to do?  If so, how do you calculate the monthly average for Aug 5th? Or the weekly average for May 15th?

I normally calculate SMA's for each row value (say mmolPL) and store the SMA in the row itself. So I would have mmolPL_SMA_5 which is the 5 period SMA (which is a 5 day SMA if each row represents a day's data)  of the mmoIPL column, and if I wanted monthly average I would have another column in the row called mmolPL_SMA_30, etc.. By storing the SMA's in the row itself, I can easily do queries that tell me if mmoIPL is > mmoIPL_SMA5 or mmoIPL > mmoIPL_SMA_30 and do ratios like mmoIPL /  mmoIPL_SMA_30 * 100 to show how much higher or lower the day's mmoIPL is compared to the 30 day SMA.

Anyway, that's how I would do it. It would give me historical SMA's for every point in time.

Barry

Barry

I'm trying to generate a simple average, not a moving one. That may come later if I decide to do some trend analysis.


Table defiition

CREATE TABLE "BloodTests"
(
"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_TimeStamp" TIMESTAMP NOT NULL,
"_xDate" VARCHAR(10) COLLATE "ANSI_CI" COMPUTED ALWAYS AS CAST(_Timestamp AS VARCHAR(10) DATE FORMAT 'dd/mm/yyyy') NOT NULL,
"_xTime" VARCHAR(5) COLLATE "ANSI_CI" COMPUTED ALWAYS AS CAST(CAST(_Timestamp AS TIME) AS VARCHAR(5) TIME FORMAT 'hh:mm') NOT NULL,
"_Group" VARCHAR(30) COLLATE "ANSI_CI" NOT NULL,
"_mmolPl" DECIMAL(19,1) DESCRIPTION 'mmol/L (millimoles per litre)',
"_mgPdl" INTEGER DESCRIPTION 'mg/dl (milligrammes per decilitre)',
"_Comments" CLOB COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID"),
CONSTRAINT "TimeStamp" UNIQUE ("_TimeStamp")
)

These are blood glucose test results (mine) and there may be none or many on a specific day (generally 3, sometimes 6 and rarely less than 3)

<<If so, then you can't use Current_Date() because this will give you one average for each period as of the current date. Or is that what you are trying to do?  If so, how do you calculate the monthly average for Aug 5th? Or the weekly average for May 15th?>>

I'm not sure I understand this. What I'm trying to do is define the period for which I will calculate averages. The examples have "CURRENT_DATE - INTERVAL '90' DAY" simply because that's the interval I entered, It could have been 30 or 365 so I can say I want a weekly average for the last 3 months or the last 3 years.

Roy Lambert

Roy

I have a "YearWeek" and a YearMonth function which I use in place of your "extract" code.

It means you can just write:
SELECT
YearWeek(<field>) as Point,

which is a bit clearer / easier:

CREATE FUNCTION "YearWeek" (IN "InputDate" DATE)
RETURNS VARCHAR(8) COLLATE "ANSI"
BEGIN
 DECLARE RESULT VARCHAR(8);
SET RESULT = CAST(EXTRACT(YEAR FROM InputDate) as VARCHAR(4))
 + '-' +

 IF(EXTRACT(MONTH FROM InputDate) = 12 THEN
   IF(EXTRACT(DAY FROM InputDate)IN (29, 30, 31) THEN
     '53'  ELSE CAST(EXTRACT(WEEK FROM InputDate) as VARCHAR(2)))
   ELSE
       IF(EXTRACT(WEEK FROM InputDate) > 9 THEN '' ELSE '0')
       + CAST(EXTRACT(WEEK FROM InputDate) as VARCHAR(2))
     );
RETURN RESULT;
END


-----------

NOTE: My function also changes an annoying ISO standard for the EXTRACT(WEEK ) function which returns "week 1" for the days December 29, 30, 31. I think it is less confusing to number these dates as "week 53"

Adam


Thanks - I may go for that.

Roy Lambert

Roy

I did also think that for pure accuracy of the averages you are probably best not use use 90 days for all the time-ranges.

"90 day" could give you up to 5 "months" each with averages (if you run the code in early March!) but some of the averages will only be for a small number of days, making them less "smooth" than your other averages.

If this is important you can find the start of the prior month, use this as your start date and work back by exactly 3 months:

Say we are on 2nd March. 2013-03-02

1. Extract Year and Month from Current_Date.
2. Subtract 1 from Month.
3. Figure out the number of days in the Month from 2.
4. Build your start date (in this example it would be 2013-02-28)
5. Add "  - INTERVAL '3' MONTH "

Bingo you have 3 months, and 3 averages each of which will be for a whole month.

Week numbers suffer in the same way ... but I guess there the variations in the average are less significant.

Adam

>I did also think that for pure accuracy of the averages you are probably best not use use 90 days for all the time-ranges.
>
>"90 day" could give you up to 5 "months" each with averages (if you run the code in early March!) but some of the averages will only be for a small number of days, making them less "smooth" than your other averages.

It doesn't really matter for this purpose. What I'll be doing is to present the data in a graphical form just to see how bad my glucose control is. Unless you're one of those freaks who don't like beer, whiskey, wine, chocolate or eating daily readings are going to vary quite a bit. EG from c4 before a meal to c12 a couple of hours after it. There's a test (HbA1c) which measures glucose levels over a period of 2 - 6 months (depends on the individual) and I'm just trying to get an idea of how that will look.

Roy