如何向每个用户显示最后两个付款日期和坐骑的总和？

发布于 2025-02-09 22:58:22 字数 270 浏览 2 评论 0 原文

我有2张表，我从表1中提取了每用户的最后2个税收日期，这些日期在19/06/2022上次征税，以及表2中的产品ID 12，以及的总和金额税收以及图像波纹图中提到的两个最后两个税日之间的时间范围。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

在梵高的星空下 2025-02-16 22:58:22

第一步是添加等级（）或 row_number（）以向后订购付款， id ，因此您只查看2个最后付款。喜欢 this 。

下一步是汇总这些步骤以获取最小日期和最大日期以及金额之和。喜欢 this this this> this> this 。

最后，您计算最小日期和最大日期之间的差异。喜欢 this this this this>

WITH LAST_TWO AS (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY id ORDER BY tax_date DESC) AS time_ago
  FROM table1
  QUALIFY time_ago <= 2
),
AGG AS (
  SELECT
    id,
    MIN(tax_date) as tax_date_MIN,
    MAX(tax_date) as tax_date_MAX,
    SUM(amount) as amount_SUM
  FROM LAST_TWO
  GROUP BY id
)
SELECT id, amount_SUM, DATEDIFF(day, tax_date_MIN, tax_date_MAX) as DATE_RANGE
FROM AGG
INNER JOIN table2 ON AGG.id = table2.id
WHERE table2.product_id = 12;

First step is to add a RANK() or ROW_NUMBER() to order the payments backwards, by id so you're only looking at the 2 last payments. Like this.

The next step is to aggregate those to get min and max dates, and sum of amount. Like this.

Lastly, you calculate the difference between min and max dates. Like this.

WITH LAST_TWO AS (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY id ORDER BY tax_date DESC) AS time_ago
  FROM table1
  QUALIFY time_ago <= 2
),
AGG AS (
  SELECT
    id,
    MIN(tax_date) as tax_date_MIN,
    MAX(tax_date) as tax_date_MAX,
    SUM(amount) as amount_SUM
  FROM LAST_TWO
  GROUP BY id
)
SELECT id, amount_SUM, DATEDIFF(day, tax_date_MIN, tax_date_MAX) as DATE_RANGE
FROM AGG
INNER JOIN table2 ON AGG.id = table2.id
WHERE table2.product_id = 12;

回复收藏 0 原文

~没有更多了~