Geek asks and answers

Oracle / sql / jpa. With a table for the year, how do I select rows with maximum data (column) every 7 days?

I have a table of the following columns:

Timestamp (timestamp), data (integer), id1 (integer), id2 (integer)

My user will be able to provide three entries, the start date (normalized until Sunday), the end date (normalized until Saturday) and the id1 list.

Just to simplify my question, if the start date is January 1, 2010, accepted as Sunday, the end date is December 31, 2010, accepted as Saturday, and list id1 is {100, 101, 102}, my question is:

How to create a β€œsingle” SQL string to collect max (data) from each 7-day period, from January 1 to December 31 for each unique identifier id1 and id2?

Can you also do this with a typical JPA request?

Please note that I use the 7-day period instead of the week, because there is no context for the week to avoid border problems, for example, what to do from December 29, 20xx, which falls on the first week of the year 20xx + 1, etc.

Thanks for your answers in advance!

-Rajan

+3
source share
1 answer

The date format 'WW'gives the week number of the year.

SELECT id1
      ,id2
      ,TRUNC(Timestamp,'WW')
      ,MAX(data)
FROM mytable
WHERE Timestamp BETWEEN :start AND :end
GROUP BY id1, id2, TRUNC(Timestamp,'WW');

If 'WW'it does not do this for you (problems with borders), instead you can take the day number ( 'DDD') and divide by 7, for example:

TRUNC(TO_NUMBER(TO_CHAR(Timestamp,'DDD'))/7)
+1
source

More articles:


All Articles