Getting peak hourly KW for each month and showing the year, month, day, and hour it occurred for pas-CodePudding

I have electric meter usage data that goes back 10 years and is stored as hour intervals. And I need to show the monthly KW (energy) peak and the year, month, day and hour it occurred grouped by the Substation, Feeder, and Rate. I don't want to show any of the other hours, only the hour it peaked at for the month.

The table has over 2 billion records, and the raw records look like:

Meter	Substation	Feeder	Rate	IntervalDate	IntervalHour	KW
11111	1	W1	Residential	2022-07-25	1	10.5
22222	1	W1	Residential	2022-07-25	1	4.5
33333	2	G1	Commercial	2022-07-25	1	20.4
44444	3	Z5	Industrial	2022-07-25	4	100.4
55555	5	J5	Commercial	2022-07-25	24	5.7

The Meter can be ignored because it's being aggregated down to the Substation, Feeder, and Rate level. There are 3 different rates, Residential, Commercial, and Industrial.

So for every month for the past 10 years, I need to show the peak KW grouping by Substation, Feeder, and Rate and which year, day, and hour the KW peak occurred.

So, the result (not using above data) would look something like:

Year	Month	Day	Hour	Substation	Feeder	Rate	Summed KW
2015	June	23	8	Residential	3	Z5	20.5
2015	June	15	9	Commercial	3	Z5	40.7
2015	June	28	7	Industrial	3	Z5	100.2
2015	July	09	6	Residential	3	Z5	25.5
2015	July	12	5	Commercial	3	Z5	48.7
2015	July	02	8	Industrial	3	Z5	143.2

Is this possible with just a query? I appreciate any guidance.

CodePudding user response：

Updated:

As indicated in the comments, this is a two-step process. Step one is summing by group. Step two is selecting the rows from Step 1 that are the summed peaks for rate types per month.

The two step process can be a CTE or a subquery. I find the CTE a bit more readable in this case.

with HOURLY_SUMS as
(
select   Substation
        ,Feeder
        ,Rate
        ,IntervalDate
        ,IntervalHour
        ,sum(KW) as TotalKW
from T1
group by Substation, Feeder, Rate, IntervalDate, IntervalHour
)
select   top 1 with ties
         Substation
        ,Feeder
        ,Rate
        ,IntervalDate PeakIntervalDate
        ,IntervalHour PeakIntervalHour
        ,TotalKW
from HOURLY_SUMS
order by row_number() over
  (partition by month(IntervalDate), Rate order by TotalKW desc)
;

SQL Fiddle with sample data.

Output:

| Substation | Feeder |        Rate | PeakIntervalDate | PeakIntervalHour | TotalKW |
|------------|--------|-------------|------------------|------------------|---------|
|          2 |     G1 |  Commercial |       2022-07-25 |                1 |    20.4 |
|          3 |     Z5 |  Industrial |       2022-07-25 |                4 |   100.4 |
|          1 |     W1 | Residential |       2022-07-25 |                1 |      15 |

CodePudding user response：

I'm not sure just try.

select to_char(to_date(intervaldate,'YYYY-MM-DD'),'YYYY') as Year, to_char(to_date(intervaldate,'YYYY-MM-DD'),'Month') as Month, to_char(to_date(intervaldate,'YYYY-MM-DD'),'DD') as Day, IntervalHour Hour, Rate Substation, Substation Feeder, Feeder Rate, KW Summed KW from t1;