SQL to average different time scopes-CodePudding

I have a table (DTP) with a simple structure and around 200k rows

 ------- -------------- ------ ----- --------- ---------------- 
| Field | Type         | Null | Key | Default | Extra          |
 ------- -------------- ------ ----- --------- ---------------- 
| DT    | datetime     | YES  |     | NULL    |                |
| PWR   | decimal(5,3) | YES  |     | NULL    |                |
| Pix   | int(11)      | NO   | PRI | NULL    | auto_increment |
 ------- -------------- ------ ----- --------- ----------------

DT = a date-time every 10 minutes (of daylight)
PWR = kW power generation avg for the 10 minute period

I want to know daily, weekly, monthly and annual average power generation

I suspected the following was wrong even before I tried it but I can't find help on how to structure my SQL to return these averages. (I'm anticipating one different query for each average per period.

SELECT AVG(PWR) AS DailyAvgPwr
FROM DTP 
WHERE (year(DT) AND month(DT) AND day(DT)) IN(
    SELECT PWR, year(DT), month(DT), day(DT)
    FROM DTP
    GROUP BY year(DT), month(DT), day(DT)
    ORDER BY year(DT), month(DT), day(DT);

It would be great to have some guidance on at least one of the periods: I'm guessing I can extrapolate the answer to get the other period averages.

[in case it's relevant, I'm using MariaDB, Server version: 10.5.12]

CodePudding user response：

I think this is a job for WITH ROLLUP. Try something like this.

SELECT YEAR(DT) YR, MONTH(DT) mon, DAY(DT) dy,
       AVG(PWR) kW
  FROM DTP
 GROUP BY YEAR(DT), MONTH(DT), DAY(DT) WITH ROLLUP