查询以找到日期之间的最大差距

发布于 2025-02-14 01:07:00 字数 361 浏览 3 评论 0原文

我有一个带有每个客户名称和日期列的表格，并想编写一个查询，以给我每个用户的间隙天数，

name date
ali 2022-01-01
ali 2022-01-04
ali 2022-01-05
ser 2022-03-01

答案应为Ali的3个，而SER的答案将为null。

这是我尝试的：

select name ,min(date) over (partition by name order by date) start_date , max(date) over (partition by name order by date) end_date from table

原文

I have a table with the name of each customer and date columns and want to write a query to give me the number of gap days for each user,

name date
ali 2022-01-01
ali 2022-01-04
ali 2022-01-05
ser 2022-03-01

the answer should be 3 for ali and for ser will be null.

here is what I tried:

select name ,min(date) over (partition by name order by date) start_date , max(date) over (partition by name order by date) end_date from table

分享到QQ

分享到微博

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

发布评论

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

信仰 2025-02-21 01:07:01

实现此目的的一种方法是使用窗口函数（例如滞后，铅）来找到先前/第二天，然后使用日期函数（例如，当前和先验）使用netatediff函数找到差异。这样的东西..

SELECT name,
       MAX(datediff(date, PreviousDate)) AS Gap
FROM (SELECT name,
             date,
             LAG(date) OVER(PARTITION BY name ORDER BY date) as PreviousDate
      FROM table t
GROUP BY name

One approach to achieve this is using a window function (like lag, lead) to find the prior/next day and then find the difference between the dates (current and prior, for example ) using datediff function. Something like this..

SELECT name,
       MAX(datediff(date, PreviousDate)) AS Gap
FROM (SELECT name,
             date,
             LAG(date) OVER(PARTITION BY name ORDER BY date) as PreviousDate
      FROM table t
GROUP BY name

回复收藏 0 原文

一向肩并 2025-02-21 01:07:01

我的方法是将每个记录与最接近的日期匹配，然后找到最大差距，然后与原始表格一起使用，以获取每个用户的差距。

这是MySQL版本：

select
    cu.name, max(cg.gap) maxgap
from
    customers cu left join
    (
    select
        c.name, datediff(min(cn.date), c.date) gap
    from
        customers c left join customers cn on c.name = cn.name
    where
        cn.date > c.date
    group by
        c.name, c.date
    ) cg
    on cu.name = cg.name
group by
    cu.name

my approach is to match every record with the closest date then find the maximum gap and left join with the original table to get the gap for each user.

here's MySQL version:

select
    cu.name, max(cg.gap) maxgap
from
    customers cu left join
    (
    select
        c.name, datediff(min(cn.date), c.date) gap
    from
        customers c left join customers cn on c.name = cn.name
    where
        cn.date > c.date
    group by
        c.name, c.date
    ) cg
    on cu.name = cg.name
group by
    cu.name

回复收藏 0 原文

~没有更多了~