Speed Phreak #2: Aunt Kathi FIFO (IT Toolbox Blogs)



Kathi Kellenberger looked at the Speed Phreak challenge #2 (FIFO). She posed and answered the question:  

 

The original winning solution from Dave Ballantyne is brilliant, but a brain twister for the uninitiated. It uses one statement comprised of several common table expressions (CTEs) and includes a couple of CROSS APPLY type joins. Was there a simpler but equally-speedy window function-based solution?

 

Having accepted the challenge, I proposed analytic-windowing function a few days back. Now let’s examine Ms Kellenberger’s approach a bit. Note that I “grafted on” the same miniscule sample data set I’d used for my query:

 

WITH
-- ------------------------------
-- Duke's grafting
-- ------------------------------
sample_data
(stockID, ArticleID, TranDate,  TranCode,Items,Price ) AS (           SELECT
       1, 10000, '10:45:07', 'IN',     38, 245.94 FROM DUAL UNION ALL SELECT
       2, 10000, '10:45:08', 'IN',    100, 245.94 FROM DUAL UNION ALL SELECT
       3, 10000, '10:45:09', 'IN',    150, 245.94 FROM DUAL UNION ALL SELECT
       4, 10000, '10:45:20', 'IN',    450, 245.94 FROM DUAL UNION ALL SELECT
       5, 10000, '12:05:25', 'OUT',   610, NULL   FROM DUAL UNION ALL SELECT
       6, 20000, '10:45:20', 'IN',    850, 245.94 FROM DUAL UNION ALL SELECT
       7, 20000, '12:05:25', 'OUT',   610, NULL   FROM DUAL UNION ALL SELECT
       8, 10000, '14:39:27', 'IN',      5, 199.95 FROM DUAL UNION ALL SELECT
       9, 10000, '14:39:28', 'RET',    10,   NULL FROM DUAL UNION ALL SELECT
      10, 10000, '14:39:29', 'IN',     15, 199.95 FROM DUAL UNION ALL SELECT
      11, 10000, '14:39:40', 'IN',     32, 199.95 FROM DUAL UNION ALL SELECT
      12, 10000, '16:14:13', 'OUT',   165, NULL   FROM DUAL UNION ALL SELECT
      13, 10000, '18:18:58', 'RET',     5, NULL   FROM DUAL UNION ALL SELECT
      14, 10000, '20:18:54', 'IN',    500, 135.91 FROM DUAL 
),
s AS (
SELECT TRUNC(ArticleID,-4) as articleID,
       ROW_NUMBER()OVER(ORDER BY trandate) AS StockID ,
       TranCode ,
       TranDate ,
       Items ,
       Price
  from sample_data
), 
-- ------------------------------
-- Kathi's code (with Duke mods)
-- ------------------------------
ItemEndTotal AS (
SELECT articleID ,
       SUM(CASE WHEN TranCode ='OUT' 
                THEN -Items
	        ELSE +Items
            END)AS FinalCount
  FROM S
 GROUP BY ArticleID
),
ReverseRunningTotal AS (
SELECT StockID ,
       articleID ,
       TranCode ,
       TranDate ,
       Items ,
       Price ,
       SUM(Items)OVER (PARTITION BY ArticleID ORDER BY TranDate
	    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) ReverseTotal,
       SUM(Items*price)OVER (PARTITION BY ArticleID ORDER BY TranDate
	    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) ReverseTotal$
  FROM S
 WHERE TranCode IN( 'IN','RET' )
),
FindDate AS (
SELECT DISTINCT
       T.ArticleID,
       FinalCount ,
       LAST_VALUE(StockID)OVER 
            (PARTITION BY P.ArticleID 
                 ORDER BY StockID -- stockID instead of TranDate
             ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS TheStockID
  FROM ItemEndTotal  T
  JOIN ReverseRunningTotal  P
    ON T.ArticleID= P.ArticleID
   AND P.ReverseTotal>= T.FinalCount
) 
SELECT RRT.ArticleID,
       FinalCount ,
        SUM(CASE WHEN TheStockID = StockID
                 THEN FinalCount- ( ReverseTotal - Items )
                 ELSE Items
             END 
           * COALESCE(Price,PurchasePrice) -- not just PurchasePrice
           ) 
         AS Value
  FROM ReverseRunningTotal RRT
  JOIN FindDate
    ON RRT.ArticleID= FindDate.ArticleID
 CROSS APPLY (SELECT Price AS PurchasePrice
                FROM ReverseRunningTotal R 
               WHERE RRT.ArticleID= R.ArticleID
            		 AND TranCode= 'IN'
		             AND R.StockID<= RRT.Stockid -- StockID, not TranDate !!!
		           ORDER BY R.StockID DESC -- StockID, not TranDate
               FETCH FIRST 1 ROW ONLY
              ) P
 WHERE  RRT.StockID >= TheStockID -- StockID, not TranDate
 GROUP BY RRT.ArticleID ,FinalCount 
 ORDER BY ArticleID, 1;

 ARTICLEID   FINALCOUNT           VALUE
---------- ------------ ---------------
     10000          530       73,953.50
     20000          240       59,025.60

Elapsed: 00:00:00.25

 

Ms. Kellenberger notes:

 

The outer query also uses a CROSS APPLY to find the prices for the returned items, which I just copied from Dave’s code.

 

I had to figure out what CROSS APPLY does in order to make (what I hope are) corrections; I elaborate below. 

 

The important result is that the query above and my earlier query match outputs.

 

<END>

 

 


 

 

Note#1:

I modified the code a bit as a result of some comparisons (testing on the first 50,000 records of the sample data set) with my code. Modifications to the Kellenberger query include:

1. Using COALESCE(Price,PurchasePrice) instead of just PurchasePrice.

2. Using StockID consistently instead of TranDate; StockID is always unique; TranDate… not so in the sample data.

 

Note#2:

This query extracts data from the stock table twice: once for the “ItemEndTotal” subquery, and once for the “ReverseRunningTotal”. The double-pull is somewhat disguised because I changed them to extract from my sample data query “S”. With the small data set you see, that’s irrelevant. However it was time-critical with the 50,000 record data set. My query took about 0.5 seconds; the one above about 30 seconds.

 

Note#3:

Two annoyances in converting SQL Server code to Oracle:

1. Removing the “AS” between a table-name and its alias. Sure the ANSI standard permits “AS” — but here Oracle falls short. I manually removed them.

2. Separating the concatenation of elements in the posting source code: for example, I had to separate “CASEWHEN” into “CASE WHEN”.

 

 


 

 

The wikipedia image represents a FIFO (first in, first out) queue. The image was created by User:Vegpuff. – Own work, CC BY-SA 3.0, https://commons.wikimedia.org/w/index.php?curid=7586271

 

 
Oracle 12c
 
SQL Server 2012
 
FIFO
 
Kathi Kellenberger
 
David Ballantyne

Source: SANS ISC SecNewsFeed @ May 5, 2017 at 06:12AM

0
Share