Teradata FIFO脚本 [英] Teradata FIFO Script

问题描述

有一个旧的堆栈帖子(先进先出(FIFO)库存成本计算)，其中包含基于集合的速度策略:FIFO库存SQL问题:( https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/).

There is an old stack post (First-in-first-out (FIFO) inventory costing) that contains the Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem: (https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/).

我一直在尝试将其从SQL Server改编为Teradata SQL，但发现:

I have been trying to adapt it from SQL Server to Teradata SQL but have discovered that:

(a)Teradata仅可使用语句处理一个CTE

(a) Teradata can only handle one CTE with statement

(b)您不能使用交叉应用

(b) You cannot use cross apply

(c)您不能使用提示索引吗?

(c) You cannot use hint indexes?

我的问题是:

在Teradata中(除易失性表之外)是否有其他方法可以绕过上面的(a)?

Is there an alternative in Teradata (other than volatile tables) to get around (a) above?

Terdata的交叉联接"与SQL Server中的交叉应用"相同吗?

Is the Terdata "Cross Join" the same as Cross Apply in SQL Server?

有人将这个脚本修改为Teradata吗?

Has anyone adapated this script to Teradata?

推荐答案

您的帖子已有几个月的历史了，但是这对其他人也可能有用.

Your post is a few months old, but nevertheless this might be useful for others, too.

当我记得几年前将其移植到Teradata时，在Teradata的Developer Exchange上也有类似的问题.快速搜索其他解决方案将我带到了这篇文章.

There was a similar question on Teradata's Developer Exchange, when i remembered that i ported it to Teradata a few years ago. A quick search for other solutions routed me to this post.

由于Teradata支持ROWS UNBOUNDED PRECEDING(微软在SS2012中添加了它)，事实证明它要简单得多:

It turned out to be much simpler due to Teradata's support of ROWS UNBOUNDED PRECEDING (Microsoft added that in SS2012):

关于您的问题:

a:CTE可以用派生表代替，这只是语法上的变化.

a: CTE can be replaced by Derived Tables, it's just a syntax variation.

b:CROSS/OUTER APPLY是SQL Server专有的语法，有时可以用[OUTER] JOIN代替，在这种情况下，这只是一种求和的复杂方法.

b: CROSS/OUTER APPLY is SQL Server proprietary syntax, which can sometimes be replaced by an [OUTER] JOIN, in this case it was just a complicated way to do a cumulative sum.

c:当优化程序没有制定好的计划时，索引提示应该是最后的选择

c: Index hints should be a last resort when the optimizer is not doing a good plan

SELECT
   ArticleId
  ,SUM(ItemCnt) AS CurrentItems -- same as TotalStock
  ,SUM(ItemCnt * CurrentPrice) AS CurrentValue
FROM
 (
   SELECT
      ArticleId

     -- how many items will be used from this transaction, maybe less than all for the oldest row
     ,CASE WHEN RollingStock + Items > TotalStock THEN TotalStock - RollingStock ELSE Items END AS ItemCnt

     -- find the latest IN-price for RET rows
     ,MAX(Price)
      OVER (PARTITION BY ArticleID, PriceGroup
            ORDER BY TranDate) AS CurrentPrice
   FROM
    (
      SELECT
         ArticleId ,TranDate ,Price ,Items --,TranCode

        -- dummy column to get the current price in the next step, new group starts with every 'IN'
        ,SUM(CASE WHEN TranCode = 'IN' THEN 1 ELSE 0 END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate
               ROWS UNBOUNDED PRECEDING) AS PriceGroup

        -- Aggregating all in/out movements -> number of items left in stock after all transactions
        ,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items ELSE -Items END)
         OVER (PARTITION BY ArticleID) AS TotalStock

        -- reverse sum of all inbound IN/RET movements
        ,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
         OVER (PARTITION BY ArticleID)
        -SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate
               ROWS UNBOUNDED PRECEDING) AS RollingStock
/*
        -- same as above, simpler syntax, but different ORDER BY results in extra STATS step in explain
        ,COALESCE(SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS RollingStock
*/
/*      -- cumulative sum, not needed to get the result
        ,SUM(CASE WHEN TranCode IN ('IN', 'RET') THEN Items ELSE -Items END)
         OVER (PARTITION BY ArticleID
               ORDER BY TranDate
               ROWS UNBOUNDED PRECEDING) AS CurrentItems
*/
      FROM Stock
      -- only keep the row needed to calculate the value
      -- plus all IN rows to find the current price for RET rows in the next step
      -- to exclude items out of stock: add "AND (TotalStock > 0)"
      QUALIFY ((TranCode = 'IN') OR (RollingStock <= TotalStock AND TranCode = 'RET'))AND (TotalStock > 0)
    ) AS dt
   -- remove older IN rows
   QUALIFY ItemCnt >= 0
 ) AS dt
GROUP BY 1
ORDER BY 1

它基于与此处所述的获奖解决方案相同的逻辑: https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

It's based on the same logic as the winning solution described here: https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

这将运行得非常快，您不必创建SQL Server所需的任何索引:-)

This will be running quite fast and you don't have to create any of the indexes needed for SQL Server :-)

将其移植到其他DBMS的注释:

Remark for porting it to other DBMSes:

这是普通的标准SQL，只有QUALIFY是特定于Teradata的.QUALIFY与HAVING for GROUP BY相同，用于过滤OLAP函数的结果.可以通过将条件移到外层的WHERE中轻松地替换它.

It's plain Standard SQL, only the QUALIFY is Teradata specific. QUALIFY is the same as a HAVING for GROUP BY, filtering the result of an OLAP function. It can be easily replaced by moving the condition into a WHERE in the outer level.

这篇关于Teradata FIFO脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！