I want to get the accumulated amount every hour in Oracle-CodePudding

`select
    order_price,
    To_char(to_date(order_date,'YYYY-MM-DD HH24:MI:SS'),'yyyymmdd hh24') as order_date, 
SUM(order_price) OVER(ORDER BY To_char(to_date(order_date,'YYYY-MM-DD HH24:MI:SS'),'yyyymmdd 
hh24')) as "bth" 
from order_tbl 
where seller_no=100 
order by order_date;`

I got this result.

But the data I want to get is as follows.

20000 / 20220524 15 / 52500

13000 / 20220524 15 / 52500

19500 / 20220524 15 / 52500

19600 / 20220524 16 / 72100

222000 / 20220524 17 / 738700

and even if there is no data...

0 / 20220524 18 / 738700

0 / 20220524 19 / 738700

0 / 20220524 20 / 738700 .

0 / 20220525 10 / 738700

13600 / 20220525 11 / 787300

like this.

I want to get the order_date and bth for every hour even if there is no data.

It's too difficult for me, but how can I do?

i will remove order_price and add distinct later.

CodePudding user response：

You can try this: (read the guide below)

db<>fiddle

WITH all_hour AS (
    SELECT LEVEL AS hour
    FROM dual
    CONNECT BY LEVEL <= 24
), 
all_date AS (
    SELECT TO_CHAR(DATE'2022-05-24'   LEVEL - 1, 'YYYYMMDD') AS dt
    FROM dual
    CONNECT BY LEVEL <= (DATE'2022-05-27' - DATE'2022-05-24'   1)
), 
all_date_hour AS (
    SELECT dt || ' ' || (CASE WHEN hour < 10 THEN '0' || TO_CHAR(hour) ELSE TO_CHAR(hour) END) AS order_date
    FROM all_date
    CROSS JOIN all_hour
), 
your_order AS (
    SELECT 
        order_price,
        TO_CHAR(TO_DATE(order_date,'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD HH24') AS order_date,
        seller_no
    FROM order_tbl
),
your_sum AS (
    SELECT adh.order_date, SUM(CASE WHEN yo.seller_no = 100 THEN yo.order_price ELSE 0 END) AS bth
    FROM all_date_hour adh
    LEFT JOIN your_order yo ON adh.order_date = yo.order_date
    GROUP BY adh.order_date
)

SELECT order_date, SUM(bth) OVER(ORDER BY order_date) AS bth
FROM your_sum
ORDER BY order_date;

Summary:

(1) Table 1 : all_hour

includes numbers ranging from 1 to 24

(2) Table 2 : all_date

includes dates from '2022-05-24' to '2022-05-27'.
if your prefer range is '2022-01-01' to '2022-12-31', just simply change '2022-05-24'(Line 7 & 9) -> '2022-01-01', and '2022-05-27'(Line 9) -> '2022-12-31'

(3) Table 3 : all_date_hour

includes dates in the format, 'YYYYMMDD HH24', e.g. '20220524 01'
it is a result from cross joining the first and second table

(4) Table 4: your_order

same as your sample table, order_tbl, just reformatting the order_date in the format, 'YYYYMMDD HH24', e.g. '20220524 01'

(5) Table 5: your_sum (NOT ACCUMULATED YET)

simple summation of order_price, group by the order_date
left join is use here so that all dates from all_date_hour is included
any additional conditions can be added inside the case statement (Line 24)
for example, see (Line 24) SUM(CASE WHEN yo.seller_no = 100 AND youradditionalcondition = somecondition THEN yo.order_price ELSE 0 END)

(6) Final Select Query:

accumulated sum is done here using SUM() OVER(ORDER BY *yourexpr*)

CodePudding user response：

You can use this query - explained later:

select
    to_date('01/01/2022 00:00:00','dd/mm/yyyy hh24:mi:ss')   all_hours.sequence_hour/24 order_date_all, 
    order_price,
    To_char(order_date, 'yyyymmdd hh24') as order_date_real,
SUM(order_price) OVER(ORDER BY To_char(order_date, 'yyyymmdd hh24')) as "bth" 
from order_tbl ,
    (SELECT LEVEL sequence_hour
    FROM dual
    CONNECT BY LEVEL <= 10) all_hours
where    trunc((order_date( )-to_date('01/01/2022 00:00:00','dd/mm/yyyy hh24:mi:ss'))*24) = all_hours.sequence_hour
and seller_no( )=100 
order by 1;

Explanation:

you don't need to perform To_char(to_date(order_date,'YYYY-MM-DD HH24:MI:SS'),'yyyymmdd hh24') - it means that you take "order_date" which is already date and convert it again to date. It is not needed
I have added maximum number of hours - it is the subquery all_hours. Just for the example it is limited to 10, you can change it to any other value
I have added starting point of time from which you want to display the data "01/01/2022" - change it if you want. Pay attention that it appears in 2 places.
I have added an outer join with order_tbl - pay attention for "( )" in where conditions. If you want to add additional conditions on order_tbl - remember to add the ( ) on the right side of the column as I did with "seller_no"