获得前两天的行计数并进行比较

Question

我正在尝试设置一个数据检查，在这里我们从表中获取今天和前一个日期的行计数。因为它没有在周末或假日装载，我不能说日期-1。

我想出了以下几点，以得到上一次约会：

SELECT
LOAD_DATE
,COUNT(LOAD_DATE) RW_COUNT
,ROW_NUMBER() OVER (ORDER BY LOAD_DATE ) AS LOAD_ROWNUM
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1

这将产生日期、计数和分配行号。

LOAD_DATE   RW_COUNT    LOAD_ROWNUM
2019-10-16  8259        1
2019-10-15  8253        2
2019-10-11  8256        3
2019-10-10  8243        4

我要把这两个最新的日期进行比较。大多数电流是“当前”，第二大电流是“优先”。那么，我希望有这样的结果集：

CURRENT_COUNT   PRIOR_COUNT DIFF_PERCENT
8259            8253        .9927

我的问题是，如何引用前两行并将它们进行比较？除非我考虑过了，否则我需要两个额外的SELECT语句:一个带有引用行1的WHERE子句，另一个带有WHERE引用行2。

我该怎么做？我有两个CTE吗？

最后，我将需要第三个选择，除以两行，并检查10%的公差。救命，我在分析瘫痪。

Answer 1

可以使用OLAP筛选OLAP-函数的结果：

SELECT
   LOAD_DATE
  ,COUNT(LOAD_DATE) AS CURRENT_COUNT
   -- previous day's count
  ,LEAD(RW_COUNT)
   OVER (ORDER BY LOAD_DATE DESC) AS PRIOR_COUNT
-- if your TD version doesn't support LAG/LEAD (i.e. < 16.10)
--,MIN(RW_COUNT) 
-- OVER (ORDER BY LOAD_DATE DESC
--       ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS PRIOR_COUNT

   ,CAST(CURRENT_COUNT AS DECIMAL(18,4)) / PRIOR_COUNT AS DIFF_PERCENT
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1
-- return the latest row only
QUALIFY ROW_NUMBER() OVER (ORDER BY LOAD_DATE DESC) = 1

检查10%的公差：

DIFF_PERCENT BETWEEN 0.9 and 1.1

在符合条件的情况下或在一个案例中，要么ANDed

Answer 2

我不知道你想要什么结果。但是，您可以使用LAG()和聚合来获得前面的值。

SELECT LOAD_DATE, COUNT(*) as RW_COUNT,
       LAG(COUNT(*)) OVER (ORDER BY LOAD_DATE) as PREV_RW_COUNT
FROM DATABASE1.TABLE1
WHERE LOAD_DATE >= DATE-6
GROUP BY 1;

你可能只想要两项罪名的区别。

Answer 3

如果您的TD版本(16.0+?)不支持LEAD/LAG，试试看：

SELECT 
  load_date, 
  RW_COUNT, 
  MAX(RW_COUNT) OVER(
    ORDER BY load_date DESC
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING -- Get previous row's value
  ) AS RW_COUNT_prev
FROM (
  SELECT load_date, COUNT(LOAD_DATE) RW_COUNT,
  FROM DATABASE1.TABLE1
  WHERE LOAD_DATE >= DATE-6
  GROUP BY 1
) src