sql - SQL:向后获取时间序列的结果
问题描述
给定的是每天保存的实体的库存值表。我想得到:
给出今天缺货(数量 = 0)且前一天没有缺货的所有实体。
而这与前天相比,前天是前天。等等。
我的方法有效,但我必须手动在 WHERE 条件下创建零件。
2019年全年查询怎么样?
#standardSQL
WITH
TableStockDaily AS (
SELECT TIMESTAMP('2019-10-10 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-10 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-10 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-11 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-11 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-11 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-12 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-12 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-12 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-13 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-13 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-13 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-14 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-14 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-14 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-15 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-15 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-15 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 0 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-16 00:00:00 UTC') AS ExportDate, 1001 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-16 00:00:00 UTC') AS ExportDate, 1002 AS EntityId, 1 as Qty UNION ALL
SELECT TIMESTAMP('2019-10-16 00:00:00 UTC') AS ExportDate, 1003 AS EntityId, 1 as Qty
)
SELECT
sd1.ExportDate AS DateOutOfStock,
sd2.ExportDate AS DateNotOutOfStock,
sd1.EntityId AS EntityId,
sd1.Qty AS Qty1,
sd2.Qty AS Qty2
FROM
TableStockDaily sd1
LEFT JOIN
TableStockDaily sd2
ON
sd1.EntityId = sd2.EntityId
WHERE
sd1.Qty = 0
AND sd2.Qty > 0
AND sd1.ExportDate > sd2.ExportDate
AND
(
(
DATE(sd1.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -1 DAY)
AND DATE(sd1.ExportDate) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -2 DAY)
AND DATE(sd2.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -2 DAY)
AND DATE(sd2.ExportDate) > DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -3 DAY)
)
OR
(
DATE(sd1.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -2 DAY)
AND DATE(sd1.ExportDate) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -3 DAY)
AND DATE(sd2.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -3 DAY)
AND DATE(sd2.ExportDate) > DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -4 DAY)
)
OR
(
DATE(sd1.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -3 DAY)
AND DATE(sd1.ExportDate) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -4 DAY)
AND DATE(sd2.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -4 DAY)
AND DATE(sd2.ExportDate) > DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -5 DAY)
)
OR
(
DATE(sd1.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -4 DAY)
AND DATE(sd1.ExportDate) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -5 DAY)
AND DATE(sd2.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -5 DAY)
AND DATE(sd2.ExportDate) > DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -6 DAY)
)
OR
(
DATE(sd1.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -5 DAY)
AND DATE(sd1.ExportDate) >= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -6 DAY)
AND DATE(sd2.ExportDate) <= DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -6 DAY)
AND DATE(sd2.ExportDate) > DATE_ADD(DATE(CURRENT_TIMESTAMP()), INTERVAL -7 DAY)
)
)
解决方案
我认为您正在寻找LAG()
. 此窗口函数可用于恢复一组记录中给定列的值(这里,记录共享相同EntityID
)并根据排序标准(这里ExportDate
):
SELECT *
FROM (
SELECT
EntityId,
ExportDate AS DateOutOfStock,
Qty AS QtyOutOfStock,
LAG(ExportDate) OVER(PARTITION BY EntityId ORDER BY ExportDate) AS DateNotOutOfStock,
LAG(Qty) OVER(PARTITION BY EntityId ORDER BY ExportDate) AS QtyNotOutOfStock
FROM
TableStockDaily sd1
) x
WHERE QtyOutOfStock = 0 AND QtyNotOutOfStock > 0
推荐阅读
- python - ThreadPoolExecutor 键盘中断
- javascript - Chrome 或 Firefox 中的 Web 开发人员模式:如何获取对象的 CSS 完整选择器?
- javascript - 未捕获的 ReferenceError:图表未在 test.html:11 中定义
- php - 如何从数据库中获取数据并映射到反应?
- javascript - 修改 svg 路径以删除填充部分
- amazon-web-services - 使用 AWS API Gateway 映射 URL 查询字符串参数
- c++ - strcpy克隆函数的C++代码问题
- c# - NuGet - 在构建时获取最新的 lib 版本
- python - 使用 dataframe.column 重命名列后,熊猫合并给出 Nan 值
- javascript - 在小数范围内调整位置