query on CSUM

Database
Enthusiast

query on CSUM

Hi Dieter,

Could you please help me to write a query to get the below desired result.Please find the below sample table data.









Date Name Meaure Cumm. Measure by Name Measure Total Cumm. Measure By Date
1-Jul A 10 10 17 17
1-Jul B 5 5
1-Jul C 2 2
1-Jul D 0 0
2-Jul A 4 14 7 24
2-Jul B 0 5
2-Jul C 3 5
2-Jul D 0 0
3-Jul A 15 29 23 47
3-Jul B 0 5
3-Jul C 0 5
3-Jul D 8  

expecting your response at the earliest.

14 REPLIES
Junior Contributor

Re: query on CSUM

Sorry, but this is not readabla/understandable.

You should provide DDL and some INSERTs.

Dieter

Re: query on CSUM

Hi Dieter,

the table data is like below.

Date                             Name                      Measure

2013-07-01                    A                            10

2013-07-01                    B                            5

2013-07-01                    C                            2

2013-07-02                    A                            4

2013-07-02                    C                            2

2013-07-03                    A                             15

2013-07-03                    D                              8

My output  should be like below:

name              mesure              date             CUM_AMT

A                     10                   7/1/2013         10

A                       4                   7/2/2013          14

A                     15                   7/3/2013         29

B                        5                   7/1/2013         5

B                        0                    7/2/2013         5

B                        0                    7/3/2013         5

C                        2                      7/1/2013       2

C                        3                      7/2/2013       3

C                        0                       7/3/2013      5

D                        0                       7/1/2013        0

D                        0                          7/2/2013      0

D                        8                         7/3/2013      8

like this so on... i have to get the cumulative sum for the dates which are not present in source table also...My source table is having 139 M records..

help me out in writing sql....

Regards,

Ramaiah

Enthusiast

Re: query on CSUM

You need to use window aggregate function, something like this should work...

SEL NAME, MEASURE, DATE,
SUM(MEASURE) OVER(PARTITION BY NAME ORDER BY DATE ASC ROWS UNBOUNDED PRECEDING) AS CUM_AMT
FROM <<TABLE_NAME>>
ORDER BY NAME, DATE ASC;
Enthusiast

Re: query on CSUM

Thank you dietier... but my requirement is not this...

I want to get the cumulative sum for the missing dates in the source table .....

example...

In the above source table i do not have any transactions for 02 nd and 3rd jul 2013... but still i need cumulative sum for these dates also like below...

if any user transacation is missing in the source table the the previous date cum sum should be get the in missing dates.........

B                        5                   7/1/2013         5

B                        0                    7/2/2013         5

B                        0                    7/3/2013         5

Enthusiast

Re: query on CSUM

Hi Dieter,

We are trying to find the missing dates from the source table with the sys calendar date.

the query is like below.

SEL NAME, MEASURE, DATE,

SUM(MEASURE) OVER(PARTITION BY NAME ORDER BY DATE ASC ROWS UNBOUNDED PRECEDING) AS CUM_AMT

FROM <<TABLE_NAME>>

ORDER BY NAME, DATE ASC;


but this is going to product join....  is there any alternate is there for this quwery design..

because my actual source table is having 139 M records... so it will take longer time ( 5 hrs)..

Regards,

Ramaiah

Enthusiast

Re: query on CSUM

SEL t1.name, t2.calendar_date

FROM (SEL name FROM hbarch.ram GROUP BY 1) AS t1, sys_calendar.Calendar AS t2

WHERE t2.calendar_date NOT IN (SEL tran_date FROM hbarch.ram WHERE name = t1.name)

AND t2.calendar_date >=(SEL MIN(tran_date) FROM hbarch.ram WHERE name = t1.name)

AND t2.calendar_date <= CURRENT_DATE

Junior Contributor

Re: query on CSUM

Hi Ramaiah,

what's your release? In TD13.10 you can use EXPAND ON to get the missing dates. 

How is the minimum/maximum date determined, is it the same for all names or different for each name? 

Dieter

Enthusiast

Re: query on CSUM

Hi Dieter,

13.10 version we are using.

Regards,

Ramaiah

Junior Contributor

Re: query on CSUM

Hi Ramaiah,

you reveal information only one at a time :-)

So again: How is the minimum/maximum date determined, is it the same for all names or different for each name? 

Dieter