mysql - 与前一天相比获得和失去的访问者数量 - sql

Question

CREATE TABLE visitors (
Date DATE,
visitor VARCHAR(20));

INSERT INTO visitors VALUES
("2019-10-01", "v1"),
("2019-10-01", "v2"),
("2019-10-01", "v3"),
("2019-10-02", "v2"),
("2019-10-03", "v2"),
("2019-10-03", "v4"),
("2019-10-03", "v5");

请查找每日期的访问人数，与前一天相比获得的访问人数，与前一天相比失去的访问人数。上表的预期结果应该是：

Date        | number_of_visitors | number_of_visitors_gained | number_of_visitors_lost
2019-10-01  |        3           |             3             |            0
2019-10-02  |        1           |             0             |            2
2019-10-03  |        3           |             2             |            0

显然，最具挑战性的部分是如何获得最后两列。注意，由于第一天没有前一天，所以number_of_visitors_gained是第一天的访客总数，并且number_of_visitors_lost是0。

标签： mysqlsql

Answer 1

如果您的 RDBMS 支持窗口函数，您可以聚合，然​​后使用lag()：

select 
    date,
    number_of_visitors,
    case 
        when lag(number_of_visitors) over(order by date) is null 
            then number_of_visitors
        when lag(number_of_visitors) over(order by date) < number_of_visitors
            then number_of_visitors - lag(number_of_visitors) over(order by date)
        else 0
    end number_of_visitors_gained,
    case when lag(number_of_visitors) over(order by date) > number_of_visitors
            then lag(number_of_visitors) over(order by date) - number_of_visitors
        else 0
    end number_of_visitors_lost
from (
    select date, count(*) number_of_visitors 
    from visitors 
    group by date
) t
order by date

DB Fiddle 上的演示：

日期 | number_of_visitors | number_of_visitors_gained | number_of_visitors_lost
:--------- | -----------------: | ------------------------------------: | ----------------------:
2019-10-01 | 3 | 3 | 0
2019-10-02 | 1 | 0 | 2
2019-10-03 | 3 | 2 | 0