Sumup value for every 5 mins grouping-CodePudding

As, I was unable to generate the query to get the resultset of every 5 mins of data between the two dates and group it for the tags

Earlier, I have posted in the below link but now the requirement has changed Sum up data for every 30 mins between two different datetimes - Oracle SQL query

There is a requirement where I need to apply the formula's of every times of arrival data. Im unable to proceed with the grouping. To explain it more ellaborately

Below is the data arrival (Reading-- Table)

RDNG_DT	TAG	ST_TIME	END_TM	VALUE
10-Jan-23	ALB	10-Jan-23	10-Jan-23	2
10-Jan-23	ALB	10-Jan-23	10-Jan-23	4
10-Jan-23	BCD	10-Jan-23	10-Jan-23	6
10-Jan-23	BCD	10-Jan-23	10-Jan-23	8
10-Jan-23	BAT	10-Jan-23	10-Jan-23	2
10-Jan-23	BAT	10-Jan-23	10-Jan-23	8

For the clarification, I used the to_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI') AS START_TIME,to_CHAR(END_TIME, 'DD-MON-YYYY HH24:MI') AS START_TIME

RDNG_DT	TAG	ST_TIME	END_TM	VALUE**
10-Jan-23	ALB	10-JAN-23 12.00.00.000000000 AM	10-JAN-23 12.00.05.000000000 AM	2
10-Jan-23	ALB	10-JAN-23 12.05.00.000000000 AM	10-JAN-23 12.10.00.000000000 AM	4
10-Jan-23	BCD	10-JAN-23 12.00.00.000000000 AM	10-JAN-23 12.00.05.000000000 AM	6
10-Jan-23	BCD	10-JAN-23 12.05.00.000000000 AM	10-JAN-23 12.10.00.000000000 AM	8
10-Jan-23	BAT	10-JAN-23 12.00.00.000000000 AM	10-JAN-23 12.00.05.000000000 AM	2
10-Jan-23	BAT	10-JAN-23 12.05.00.000000000 AM	10-JAN-23 12.10.00.000000000 AM	8

Now, need the grouping of every 5 mins of tags as below .

RDNG_DT	TAG	ST_TIME	END_TM	VALUE
10-Jan-23	ALB	10-JAN-23 12.00.00.000000000 AM	10-JAN-23 12.00.05.000000000 AM	2
10-Jan-23	BCD	10-JAN-23 12.00.00.000000000 AM	10-JAN-23 12.00.05.000000000 AM	4
10-Jan-23	BAT	10-JAN-23 12.00.00.000000000 AM	10-JAN-23 12.00.05.000000000 AM	2
10-Jan-23	ALB	10-JAN-23 12.05.00.000000000 AM	10-JAN-23 12.10.00.000000000 AM	4
10-Jan-23	BCD	10-JAN-23 12.05.00.000000000 AM	10-JAN-23 12.10.00.000000000 AM	8
10-Jan-23	BAT	10-JAN-23 12.05.00.000000000 AM	10-JAN-23 12.10.00.000000000 AM	8

Based on the above table, the new column must apply the formula for the fist set of data of every 5 mins

New colum RST: Case when ALB.value BCD.value BAT.value > 0 THEN ALB.value BCD.value BAT.value ELSE 0 END AS 'AFC'

As a result, grouping occurs for every 5 mins for the tags availability and need to apply the formula. The final resultset looks like as below

TAG	ST_TIME	END_TM	VALUE
RST	JAN-23 12.00.00.000000000 AM	JAN-23 12.00.05.000000000 AM	8
	JAN-23 12.05.00.000000000 AM	JAN-23 12.10.00.000000000 AM	20

I used the below query

WITH TST AS
  (
  SELECT 
      RDNG_DT,
       TAG,
       cast(ST_TIME as timestamp),
       cast(END_TM as timestamp),
       VALUE      
      FLOOR((EXTRACT(HOUR FROM cast(start_time as timestamp)) * 60   EXTRACT(MINUTE FROM cast(start_time as timestamp)))/30) AS FL
      
        FROM  Reading 
    WHERE TAG IN ('ALB','BCD','BAT') AND RDNG_DT = '10-JAN-23')
    
    SELECT  RDNG_DT,TAG,RDNG,st_time,ed_time 
    FROM TST
    group by TAG,RDNG,st_time,ed_time

CodePudding user response：

try using grouping sets

select 
      decode(grouping(nro_carga),1, item.descr, 'Total da carga ' ) descr
    , decode(grouping(cd_item),1,nro_carga, cd_item) id
    , sum(qtde_item) qtde

  from tbl1 

  inner join tbl2
    on 1 = 1

where condition = condition

group by
   grouping sets 
   (  
      nro_carga 
    , ( cd_item, item.descr )
   ), 
   ()
order by cd_item
      ,item.descr
      , nro_carga;

CodePudding user response：

Youu dont need timestamp to work with hours and minutes, you need date datatype as it contains date and time. Lets take your first table (reading table) as sample data and make dates of DATE datatype (if they aren't already):

WITH
    tbl (RDNG_DT, TAG, ST_TIME, END_TIME, A_VALUE) AS
        (
            Select To_Date('10-JAN-23', 'dd-MON-yy'), 'ALB', To_Date('10-JAN-23 12:00', 'dd-MON-yy hh24:mi'), To_Date('10-JAN-23 12:05', 'dd-MON-yy hh24:mi'), 2 From Dual Union All
            Select To_Date('10-JAN-23', 'dd-MON-yy'), 'ALB', To_Date('10-JAN-23 12:05', 'dd-MON-yy hh24:mi'), To_Date('10-JAN-23 12:10', 'dd-MON-yy hh24:mi'), 4 From Dual Union All
            Select To_Date('10-JAN-23', 'dd-MON-yy'), 'BCD', To_Date('10-JAN-23 12:00', 'dd-MON-yy hh24:mi'), To_Date('10-JAN-23 12:05', 'dd-MON-yy hh24:mi'), 6 From Dual Union All
            Select To_Date('10-JAN-23', 'dd-MON-yy'), 'BCD', To_Date('10-JAN-23 12:05', 'dd-MON-yy hh24:mi'), To_Date('10-JAN-23 12:10', 'dd-MON-yy hh24:mi'), 8 From Dual Union All
            Select To_Date('10-JAN-23', 'dd-MON-yy'), 'BAT', To_Date('10-JAN-23 12:00', 'dd-MON-yy hh24:mi'), To_Date('10-JAN-23 12:05', 'dd-MON-yy hh24:mi'), 2 From Dual Union All
            Select To_Date('10-JAN-23', 'dd-MON-yy'), 'BAT', To_Date('10-JAN-23 12:05', 'dd-MON-yy hh24:mi'), To_Date('10-JAN-23 12:10', 'dd-MON-yy hh24:mi'), 8 From Dual 
    )

Having dates as dates - you can try the below code to do the TAGs checking, VALUE summing, grouping and where filtering as you need. Here is the SQL:

SELECT
    TAG, RDNG_DT, ST_TIME, END_TIME, Sum(TAG_VALUES) "TAG_VALUES"
FROM
    (   Select
            'RST' "TAG",
            RDNG_DT,
            LPAD(FLOOR(  ( (ST_TIME - TRUNC(ST_TIME, 'dd')) * 24 * 60 ) / 60 ), 2, '0') || ':' ||                                                     
            LPAD( ( (ST_TIME - TRUNC(ST_TIME, 'dd')) * 24 * 60 ) - (FLOOR(  ( (ST_TIME - TRUNC(ST_TIME, 'dd')) * 24 * 60 ) / 60  ) )  * 60, 2, '0') "ST_TIME",
            --
            LPAD(FLOOR(  ( (END_TIME - TRUNC(END_TIME, 'dd')) * 24 * 60 ) / 60 ), 2, '0') || ':' ||
            LPAD( ( (END_TIME - TRUNC(END_TIME, 'dd')) * 24 * 60 ) - (FLOOR(  ( (END_TIME - TRUNC(END_TIME, 'dd')) * 24 * 60 ) / 60  ) )  * 60, 2, '0') "END_TIME",
            --
            CASE WHEN TAG IN('ALB', 'BCD', 'BAT') THEN A_VALUE ELSE 0 END "TAG_VALUES"
        From
            tbl order by ST_TIME
    )
Where RDNG_DT = To_Date('10-JAN-23', 'dd-MON-yy')
Group By TAG, RDNG_DT, ST_TIME, END_TIME
Order By TAG, RDNG_DT, ST_TIME, END_TIME


TAG RDNG_DT   ST_TIME END_TIME TAG_VALUES
--- --------- ------- -------- ----------
RST 10-JAN-23 12:00   12:05            10 
RST 10-JAN-23 12:05   12:10            20