SQL 如何友好显示月内多个日期 *

有数据库表TBLDATES,数据如下所示:

DATES

2020-08-08

2020-08-10

2020-08-11

2020-08-12

2020-08-16

2020-09-20

2020-09-27

2020-09-28

2020-09-29

2020-09-30

2020-10-01

2020-10-02

需要将其按年、月分组,将组内连续天用短横分割,不连续的用逗号分割,结果如下所示:

year

month

days

2020

8

8,10-12,16

2020

9

20,27-30

2020

10

1-2

 

按日期有序,对日期的年、月分组,将连续日的首尾用短横(-)拼接,不连续的用逗号拼接。

MySqlSQL

with_counter AS (

  SELECT

    *

  , CASE WHEN LAG(DATES) OVER(PARTITION BY MONTH(DATES) ORDER BY DATES) + 1 < DATES 

           OR LAG(DATES) OVER(PARTITION BY MONTH(DATES) ORDER BY DATES) IS NULL

      THEN 1

      ELSE 0

    END AS counter

  FROM TBLDATES

)

,

with_session AS (

  SELECT

    *

  , SUM(counter) OVER(ORDER BY MONTH(DATES), DAY(DATES)) AS session

  FROM with_counter

)

 

SELECT

    CAST(MIN(DAY(DATES)) AS VARCHAR(2)) ||CASE WHEN COUNT(*) = 1

      THEN ''

      ELSE '-'||CAST(MAX(DAY(DATES)) AS VARCHAR(2))

    END

  AS daylit

DAY(MIN(DATES)) AS d

MONTH(MIN(DATES)) AS mn

, TO_CHAR(MIN(DATES),'Month'AS mth

YEAR(MIN(DATES)) AS yr

FROM with_session

GROUP BY session

ORDER BY 3,2;

 

这道题其实并不难,先把日期按年、月分组,把每个月的那些日期按是否连续分组(当前日减去上一条记录的日,差等于1则连续,否则不连续),比如题中8月的日,分组后,第一组是8,第二组是101112,第三组是16,然后只需要把组内个数大于1的首尾数字用短横(-)拼接,整组再用逗号拼接即可。但是SQL没有直接提供按连续条件的分组,只能按列数据做等值分组,需要繁琐的办法,先人为造出可以用于等值分组的列数据再分组。

 

用开源集算器的SPL就很容易写:


A

1

=connect("MYSQL")

2

=A1.query@xi("SELECT * FROM TBLDATES ORDER BY DATES")

3

=A2.group(year(~):year,month(~):month;~.(day(~)).group@i(~-~[-1]!=1).(if(~.len()>1,~.m(1)/"-"/~.m(-1),~.m(1))).concat@c():days)

SPL直接支持按连续条件分组,很容易实现按条件进行分组的计算。

 

问答搜集

https://stackoverflow.com/questions/64116840/sql-formatting-to-user-friendly-date