主页 / dba / 问题 / 315927
Accepted
Saeed
Asked: 2022-08-23 22:40:22 +0800 CST

计算字段值没有变化的天数

  • 772

请考虑下表：

用户 日期 数量
约翰 2022 年 1 月 1 日 40
约翰 2022 年 1 月 2 日 -30
约翰 2022 年 1 月 6 日 50
... ... ...
杰克 2022 年 4 月 1 日 80
杰克 2022 年 4 月 1 日 -60

我们有约翰的三条记录和杰克的两条记录。在每个日期中，用户要么向金额字段添加一个值（正值），要么从中减去一个值（负值）。

金额总和未发生变化的天数很重要。

例如，从1/1/2022到1/2/2022，dear John 的值是 40。但是在1/2/2022，金额减少了 30。因此，30 天（从1/1到1/2 ), 数量等于 40, 直到1/6 , 总量 ( 40 - 30 ) 保持不变约 120 天。最后，直到今天（今天是8/23），金额的总和将等于40 - 30 + 50 = 60。

问题：

  • 如何计算这些间隔以获得天数？
  • 如何为所有用户获得这些间隔？

我阅读了有关 Window 或 CTE 的信息，但无济于事。

我为什么问这个问题？

数据库记录的数量已经增加到大约一百万条，对于 21,000 个用户，我们必须获取每个区间的长度并乘以之前记录的总量。

预期输出：

例如，对于 John，我希望得到以下输出：

40 × 30(days) + (40 - 30) × 60 (days) + (40 - 30 + 50) × 53 (days)
postgresql recursive

1 个回答

  1. Best Answer

    为了回答您的问题，我执行了以下操作（下面的所有代码都可以在此处的小提琴中找到）：

    CREATE TABLE test
(
  t_user TEXT    NOT NULL,
  t_date DATE    NOT NULL,
  amount INTEGER NOT NULL
);

    填充：

    INSERT INTO test VALUES
('John', '2022-01-01',  40),
('John', '2022-02-01', -30),
('John', '2022-06-01',  50);

    第一关：

    我留下了几个额外的字段，以便您可以遵循逻辑 - 我已将它们删除以进行最后的传递。

    SELECT
  t_user, 
  t_date, 

  CASE
    WHEN LEAD(t_date) OVER w IS NULL
      THEN current_date
    ELSE LEAD(t_date) OVER w
  END AS ld,
  
  CASE
    WHEN LEAD(t_date) OVER w is null
      THEN current_date - t_date
    ELSE LEAD(t_date) OVER w - t_date
  END AS d_cnt,
  amount,
  
  SUM(amount) OVER (PARTITION BY t_user ORDER BY t_date) AS rr_tot

FROM
  test
WINDOW w AS (PARTITION BY t_user ORDER BY t_date)
ORDER BY t_user DESC, t_date;

    结果：

    t_user      t_date          ld  d_cnt  amount  s_tot
John    2022-01-01  2022-02-01     31      40     40
John    2022-02-01  2022-06-01    120     -30     10
John    2022-06-01  2022-08-23     83      50     60

    • r_tot（运行总计）跟踪每个日期范围内的总计，r_tot * d_cnt这是最后一次通过中的乘法步骤 ( )所必需的

    • 该WINDOW w子句只是（真的）有助于易读性的一点语法糖

    最后一关：

    SELECT
  t_user, 
  SUM(r_tot * d_cnt)
FROM
(
  SELECT
    t_user, 
    CASE
      WHEN LEAD(t_date) OVER w is null
        THEN current_date - t_date
      ELSE LEAD(t_date) OVER w - t_date
    END AS d_cnt,
    amount,
    SUM(amount) OVER (PARTITION BY t_user ORDER BY t_date) AS r_tot
  FROM
    test
  WINDOW w AS (PARTITION BY t_user ORDER BY t_date)
  
) AS sub1
GROUP BY t_user
ORDER BY t_user;

    结果：

    t_user   sum
John    7420

    有几点需要注意：

    • 请使用ISO 8601 标准日期 - 该格式是YYYY-MM-DD- 它还使您的代码更易于移植、普遍理解和明确。标准的存在是有原因的，除非您有充分的理由不使用，否则应该使用标准！

    • 将来，您能否提供一下您的表格和数据？这为问题提供了一个单一的事实点 ( SPOT )，并代表那些试图帮助您的人节省了重复工作

    • 不要使用关键字（和/或保留字） ，例如DateandUser作为列名或表名 - 这会使 SQL 的可移植性降低、可读性降低并且更容易出错

    • 1

相关问题