recursively get previous row using OLAP functions
portaldev
I'm trying to calculate the number of items left based on the
following:
if date = current date then num items = X
else num items = prev num of items - Y
so in SQL:
SELECT CASE
WHEN date = current date THEN ( SELECT X FROM ... )
ELSE "prev items left" - (SELECT Y FROM ...)
END as items_left
I can get items left from the previous row using something like:
MAX(items_left) OVER( PARTITION BY MONTH(date)
ORDER BY date ROWS BETWEEN 1 preceding AND 1 preceding )
but I can't use it inside it's self e.g.
SELECT CASE
WHEN t.date = current date THEN ( SELECT X FROM ... )
ELSE
MAX(items_left) OVER( PARTITION BY MONTH(date)
ORDER BY date ROWS BETWEEN 1 preceding AND 1 preceding ) -
(SELECT Y FROM ...)
END as items_left
I am confused as how to do this, so any help would be greatly
appreciated.
Serge Rielau
"SQL on Fire (Part I)".
You can download the latest iteration of it here:
http://www.sirdug.org/November_19_2009.htm
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
Tonkuma
If result in Guess 4) was right, I have some solutions using OLAP
functions.
If this was wrong, would you show me sample data like in Guess 4).
Guess 1) following resemble terms in your descriptions or (psudo)code
are same item.
"number of items left"
"num items"
"items left"
"items_left"
Guess 2)
> if date = current date then num items = X
> else num items = prev num of items - Y
items_left will be set to X if date = current date
without considering previous items_left.
Guess 3)
> I can get items left from the previous row using something like:
> MAX(items_left) OVER( PARTITION BY MONTH(date)
> ORDER BY date ROWS BETWEEN 1 preceding AND 1 preceding )
items_left will be set to zero at the first day of the month.
Because, "PARTITION BY MONTH(date)" was specified.
Guess 4) items_left will be the following, if current_date is
'2009-11-15'.
date x y items_left
---------- --- --- ----------
2009-10-30 250 99 ???
2009-11-02 30 13 -13
2009-11-03 90 16 -29
2009-11-05 70 15 -44
2009-11-10 50 11 -55
2009-11-15 100 7 100 -- current_date
2009-11-18 40 14 86
2009-11-25 10 19 67
2009-11-26 60 12 55
2009-11-29 20 17 38
2009-11-30 80 18 20
2009-12-03 80 77 ???
Tonkuma
> Guess 4) items_left will be the following, if current_date is
> '2009-11-15'.
> date x y items_left
> ---------- --- --- ----------
> 2009-10-30 250 99 ???
> 2009-11-02 30 13 -13
> 2009-11-03 90 16 -29
> 2009-11-05 70 15 -44
> 2009-11-10 50 11 -55
> 2009-11-15 100 7 100 -- current_date
> 2009-11-18 40 14 86
> 2009-11-25 10 19 67
> 2009-11-26 60 12 55
> 2009-11-29 20 17 38
> 2009-11-30 80 18 20
> 2009-12-03 80 77 ???
------------------------------ Commands Entered
------------------------------
WITH
test_data1(current_date_) AS (
VALUES DATE('2009-11-15')
)
,test_data2 AS (
SELECT DATE(date) AS date
, x, y
FROM (VALUES
('2009-10-30', 250, 99)
, ('2009-11-02', 30, 13)
, ('2009-11-03', 90, 16)
, ('2009-11-05', 70, 15)
, ('2009-11-10', 50, 11)
, ('2009-11-15', 100, 7)
, ('2009-11-18', 40, 14)
, ('2009-11-25', 10, 19)
, ('2009-11-26', 60, 12)
, ('2009-11-29', 20, 17)
, ('2009-11-30', 80, 18)
, ('2009-12-03', 80, 77)
) t(date, x, y)
)
SELECT q.*
, MAX( CASE date WHEN current_date_ THEN x + y ELSE 0 END )
OVER( PARTITION BY MONTH(date)
ORDER BY date )
-
SUM( y )
OVER( PARTITION BY MONTH(date)
, SIGN( SIGN( date - current_date_ ) + 1 )
ORDER BY date )
AS items_left
, current_date_
FROM test_data1 p
, test_data2 q
;
------------------------------------------------------------------------------
DATE X Y ITEMS_LEFT CURRENT_DATE_
---------- ----------- ----------- ----------- -------------
2009-10-30 250 99 -99 2009-11-15
2009-11-02 30 13 -13 2009-11-15
2009-11-03 90 16 -29 2009-11-15
2009-11-05 70 15 -44 2009-11-15
2009-11-10 50 11 -55 2009-11-15
2009-11-15 100 7 100 2009-11-15
2009-11-18 40 14 86 2009-11-15
2009-11-25 10 19 67 2009-11-15
2009-11-26 60 12 55 2009-11-15
2009-11-29 20 17 38 2009-11-15
2009-11-30 80 18 20 2009-11-15
2009-12-03 80 77 -77 2009-11-15
12 record(s) selected.
Tonkuma
> ...
> "SQL on Fire (Part I)".
XMLELEMENT was used for "Aggregate concatenation" in the reference.
Like this:
SELECT
Dept,
TRIM
(',' FROM
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME a, name)
ORDER BY name)
AS VARCHAR(60)), '<A>', ''),
'</A>', ',')) AS Names
FROM Employee
GROUP BY Dept;
But, I prefer to use XMLTEXT for "Aggregate concatenation".
Because, it is a little simpler(no tags added) than using XMLELEMENT.
Like this:
SELECT Dept
, SUBSTR(
XMLSERIALIZE(
XMLAGG( XMLTEXT(',' || name)
ORDER BY name)
AS VARCHAR(60) )
, 2) AS Names
FROM Employee
GROUP BY Dept
;
Serge Rielau
Tonkuma
But, I realized recently usage of the function in this context.
portaldev
appreciated.
portaldev
distribute an amount over a month, the amount can change.
table "start": year, month, monthly_total
table "end": date, num_of_items, monthly_running_total
I select a row from "start" and insert a month of data into "end".
I do this daily, for the current date until a year later.
For the current date I know:
items_left = monthly_total - the previous days monthly_running_total
num_of_items = ceiling( items_left / days_left )
For the current date + 1 day I know:
items_left = previous days items_left - previous days num_of_items
num_of_items = ceiling( items_left / days_left )
Tonkuma
> the problem in detail:
> distribute an amount over a month, the amount can change.
>
> table "start": year, month, monthly_total
>
> table "end": date, num_of_items, monthly_running_total
>
> I select a row from "start" and insert a month of data into "end".
'2009-12-03' to '2009-12-31'?
> I do this daily, for the current date until a year later.
"I do this daily"?
On '2009-12-03', insert into "end" from '2009-12-03' to '2009-12-31'?
On '2009-12-04', insert into "end" from '2009-12-04' to '2009-12-31'?
On '2009-12-05', insert into "end" from '2009-12-05' to '2009-12-31'?
So on....
Duplicate date in "end"?
"until a year later"? Does it contradict "a month of data"?
>
> For the current date I know:
> items_left = monthly_total - the previous days monthly_running_total
> num_of_items = ceiling( items_left / days_left )
"items_left" is not in tables.
So, do you want to calculete "items_left" for the calculation of
"num_of_items" only?
In other word, is it a temporary result and throw away after
calculation of "num_of_items"?
What is "days_left"?
I couldn't find a definition of it.
If "days_left" is zero, what result of "ceiling( items_left /
days_left )" do you expect?
How to calculate "monthly_running_total"?
>
> For the current date + 1 day I know:
> items_left = previous days items_left - previous days num_of_items
> num_of_items = ceiling( items_left / days_left )
From whare do you get "previous days items_left"?
"items_left" is not kept in tables "end" or "start".
"For the current date + 2 day" is this right?
items_left = **previous days** items_left - **previous days**
num_of_items
Replace **previous days** with current date + 1 day.
portaldev
I've sent you a simple spreadsheet, I don't expect you to spend any
time on it,
it's just the easiest way to explain what I mean.
Tonkuma
broken.
Tonkuma
SELECT Dept
, SUBSTR(
XMLCAST(
XMLGROUP( ',' || name AS a
ORDER BY name)
AS VARCHAR(60) )
, 2) AS Names
FROM Employee
GROUP BY Dept
;
Also, see this:
http://www.dbforums.com/db2/1649748-simple-specs.html
(I don't know why I also used XMLTEXT in the example.)
Serge,
do you know any restriction or performance issue for using XMLGROUP in
this context?
Tonkuma
> Using XMLGROUP would be simpler on DB2 9.5 for LUW or later.
>
> SELECT Dept
> , SUBSTR(
> XMLCAST(
> XMLGROUP( ',' || name AS a
> ORDER BY name)
> AS VARCHAR(60) )
> , 2) AS Names
> FROM Employee
> GROUP BY Dept
> ;
>
SELECT Dept
, SUBSTR(
XMLCAST(
Serge Rielau
No clue. I'm the _SQ_L architect. :-)
I know a lot about very little reaching asympotically the point where I
know everything about nothing..