时间戳

首页 > 解决方案 > MYSQL 无法计算日期范围内特定值的出现次数

mysql - MYSQL 无法计算日期范围内特定值的出现次数

问题描述

所以，有一个帐号，我们有关于他们付款的每日信息。假设我们有直到今天的 1 年的信息，即 2019 年 3 月 8 日，我想计算他/她在过去 1 周内多付的次数。我使用了 mysql 窗口功能，但由于某种原因它似乎不起作用

@GMB 示例数据如下所示：假设对于这个帐户，我们有 2018 年 3 月的信息。我只想要从我文件上的最后一个日期（即今天 - 3 月 8 日）起的paid_status = overpaid 的次数/2019 和之前的 7 天、14 天、1 个月或我选择的任何持续时间。您的查询将仅对其进行硬编码 7 天。

ACCOUNT_ID paid_status amt dte
-----------------------
1234      overpaid   100 01/March/2018
.
.
.
1234      overpaid   120 01/March/2019
1234      not paid   0   02/March/2019
1234      overpaid   110 03/March/2019
1234      overpaid   120 04/March/2019
1234      overpaid   130 05/March/2019
1234      overpaid   120 06/March/2019
1234      overpaid   120 07/March/2019
1234      overpaid   121 08/March/2019

询问：

,COUNT(CASE WHEN paid_status = 'OVERPAID' THEN 1 END)
 over (PARTITION BY ACCOUNT_ID
       ORDER BY DTE ROWS BETWEEN 7 PRECEDING AND UNBOUNDED FOLLOWING
 ) AS num_times_overpaid_week1

输出应该是这样的（不包括今天的信息）：

account_id num_times_overpaid_week1
1234               6

虽然我为同一个 account_id 获得了多行，但它并没有正确地计算该字段

标签： mysqlsql

解决方案

从您的示例数据看来，您正在寻找一个简单的聚合查询（不需要窗口函数）：

SELECT account_id, SUM(paid_status = 'OVERPAID') AS num_times_overpaid_week1
FROM mytable
WHERE dte >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY account_id

ExpressionSUM(paid_status = 'OVERPAID')使用了一个很好的 MySQL 特性，条件满足时返回1，不满足0时返回。

注意：如果出于某种原因，您确实想使用窗口函数（可能执行其他计算），那么您需要使用ROW_NUMBER()按日期对记录进行排名，并在外部查询中仅过滤掉每个帐户的最新记录. 我认为窗口的定义可以大大简化：

SELECT *
FROM (
    SELECT 
        account_id, 
        SUM(paid_status = 'OVERPAID') OVER(PARTITION BY account_id) AS num_times_overpaid_week1,
        -- possibly other columns
        ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY dte DESC) rn
    FROM mytable
    WHERE dte >= CURRENT_DATE - INTERVAL 7 DAY
) x WHERE rn = 1

推荐阅读