how to group data by 7 days and output at first day of each 7 days?-CodePudding

I am using Oracle SQL, I'd like to group data by week( or # days), then output data the first day of every # of days.

For example, if I have date from 2022-01-01, to 2022-02-01, I'd like to output data every 7 days like this

2022-01-01, 2022-01-08, 2022-01-15, 2022-01-22, 2022-01-29,

Here is my code for daily data

start='2022-01-01'
end='2022-03-01'

   sql = f"""
      SELECT 
         WELL_NAME,
         ROUND(OBV_TIME,'DDD') as "Date",
         DEPTH,
         AVG(TEMPERATURE) as "TEMPERATURE"                
      FROM THERMAL.OFM_TEMPERATURE_V
      WHERE
         AREA_NAME = 'Wolf Lake'
         AND WELL_NAME = '{well}'
         AND OBV_TIME >= TO_DATE('{start}', 'YYYY-MM-DD')
         AND OBV_TIME <= TO_DATE('{end}','YYYY-MM-DD')
         AND DEPTH>={dts_well_depth_min}

      GROUP BY WELL_NAME, ROUND(OBV_TIME,'DDD'), DEPTH
         """

CodePudding user response：

Use TRUNC(obv_time, 'IW') to truncate your date to the start of the ISO week (midnight on Monday):

sql = """
SELECT WELL_NAME,
       TRUNC(OBV_TIME, 'IW') as "Date",
       DEPTH,
       AVG(TEMPERATURE) as "TEMPERATURE"                
FROM   THERMAL.OFM_TEMPERATURE_V
WHERE  AREA_NAME = 'Wolf Lake'
AND    WELL_NAME = ?
AND    OBV_TIME >= ?
AND    OBV_TIME <= ?
AND    DEPTH    >= ?
GROUP BY
       WELL_NAME,
       TRUNC(OBV_TIME,'IW'),
       DEPTH
"""

Also, it is bad practice to use string concatenation or template strings to build queries as it introduces SQL injection vulnerabilities. You should use parametrized queries (using either ? for :identifier for the parameters) and then pass in the parameters using bind variables.

CodePudding user response：

Maybe this will help if you join this to your view.

with params (st, en , gr) as
    (select 
        to_date('2022-01-01', 'yyyy-mm-dd'),--start day
        to_date('2022-03-01', 'yyyy-mm-dd'),--end day
        7  --group size
        from dual)  
select 
    p1.st   level-1 day,
    p1.st   level-1 - mod(level-1, p1.gr) first_day_in_group,
    decode(mod(level-1, p1.gr),0,'X') is_first_day_in_group
from params p1
connect by level <= p1.en - p1.st;

Result:

DAY                  FIRST_DAY_IN_GROUP   IS_FIRST_DAY_IN_GROU
-------------------- -------------------- --------------------
01.01.2022 00:00     01.01.2022 00:00     X                   
02.01.2022 00:00     01.01.2022 00:00                         
03.01.2022 00:00     01.01.2022 00:00                         
04.01.2022 00:00     01.01.2022 00:00                         
05.01.2022 00:00     01.01.2022 00:00                         
06.01.2022 00:00     01.01.2022 00:00                         
07.01.2022 00:00     01.01.2022 00:00                         
08.01.2022 00:00     08.01.2022 00:00     X                   
09.01.2022 00:00     08.01.2022 00:00                         
10.01.2022 00:00     08.01.2022 00:00                         
11.01.2022 00:00     08.01.2022 00:00                         
12.01.2022 00:00     08.01.2022 00:00                         
13.01.2022 00:00     08.01.2022 00:00                         
14.01.2022 00:00     08.01.2022 00:00                         
15.01.2022 00:00     15.01.2022 00:00     X                   
16.01.2022 00:00     15.01.2022 00:00     
...