2团
Published on 2024-08-15 / 6 Visits
0
0

SQL生成日期序列分析

1. 前言

最近同事写了一段SQL(具体如下所示),其中部分写法以前未接触过,因此进行拆分学习。

SELECT
	date_range.date,
	COALESCE(COUNT(DISTINCT stu_sch2.create_time), 0) AS daily_orders,
	COALESCE(COUNT(DISTINCT stu_sch1.send_time), 0) AS daily_bindings
FROM
	(
	SELECT
		DATE_ADD("2023-12-11", INTERVAL seq DAY) AS date
	FROM
		(
		SELECT
			seq
		FROM
			(
			SELECT
				@row := @row + 1 as seq
			FROM
				information_schema.columns,
				(
				SELECT
					@row := -1) r1
			LIMIT 200) s) dates
	WHERE
		DATE_ADD("2023-12-11 00:00:00", INTERVAL seq DAY) <= "2024-05-10 00:00:00") date_range
LEFT JOIN
        (
	SELECT
		s.send_time
	FROM
		student s
	LEFT JOIN school sch ON
		s.school_id = sch.id
	WHERE
		s.is_deleted = 0
        ) stu_sch1 ON
	date_range.date = DATE(stu_sch1.send_time)
LEFT JOIN
        (
	SELECT
		s.create_time
	FROM
		student s
	LEFT JOIN school sch ON
		s.school_id = sch.id
	WHERE
		s.is_deleted = 0
        ) stu_sch2 ON
	date_range.date = DATE(stu_sch2.create_time)
GROUP BY
	date_range.date
ORDER BY
	date_range.date

2. 生成日期序列

需求需生成从2023-12-112024-05-10的日期序列。将此问题拆分为如下步骤:

  1. 生成数字序列;

  2. DATE_ADD函数将起始日期(2023-12-11)与数字序列相加,得到日期序列。

2.1 生成数字序列

SELECT 
  seq 
FROM 
  (
    SELECT 
      @row := @row + 1 as seq 
    FROM 
      information_schema.columns, 
      (
        SELECT 
          @row := -1
      ) r1 
    LIMIT 
      200
  ) sc

SQL解析:

  1. information_schema.columns是一个系统表,它包含MySQL服务器中所有数据库的所有表的列信息。在这里,它只是作为一个大表来提供足够的行以生成所需的200个整数。

  2. SELECT @row := -1是一个子查询,它初始化一个名为@row的变量,值为-1。

  3. @row := @row + 1 as seq是一个表达式,它在每一行上都会执行。它将@row的值增加1,并将结果赋值给seq

  4. LIMIT 200限制结果集的大小为200行。

所以,这段SQL代码的结果是一个包含200行的表,每一行都有一个名为seq的列,值从0到199。

2.2 生成日期序列

SELECT 
  DATE_ADD('2023-12-11', INTERVAL seq DAY) AS date 
FROM 
  (
    SELECT 
      seq 
    FROM 
      (
        SELECT 
          @row := @row + 1 as seq 
        FROM 
          information_schema.columns, 
          (
            SELECT 
              @row := -1
          ) r1 
        LIMIT 
          200
      ) s
  ) dates 
WHERE 
  DATE_ADD(
    '2023-12-11 00:00:00', INTERVAL seq DAY
  ) <= '2024-05-10 00:00:00'

SQL解析:

  1. SELECT @row := @row + 1 as seq:这是一个子查询,它初始化一个名为@row的变量,值为-1,然后在每一行上将@row的值增加1,并将结果赋值给seq

  2. FROM information_schema.columns, (SELECT @row := -1) r1:这里使用了information_schema.columns系统表来生成足够的行以生成所需的日期范围。(SELECT @row := -1) r1是一个子查询,用于初始化@row变量。

  3. LIMIT 200:限制结果集的大小为200行,这意味着最多可以生成200个日期。

  4. DATE_ADD('2023-12-11', INTERVAL seq DAY) AS date:使用DATE_ADD函数和seq值来生成新的日期。

  5. WHERE DATE_ADD('2023-12-11 00:00:00', INTERVAL seq DAY) <= '2024-05-10 00:00:00':这是一个过滤条件,只有满足条件的日期(即小于或等于'2024-05-10')才会被包含在结果集中。

3. 其他

3.1 COALESCE

COALESCE是一个SQL函数,它返回参数列表中的第一个非NULL值。如果所有参数都是NULL,那么COALESCE函数将返回NULL。

这是COALESCE函数的基本语法:

COALESCE(expression1, expression2, ..., expression_n)

在这个语法中,expression1expression2等是要测试的表达式。COALESCE函数将从左到右评估这些表达式,返回第一个非NULL的表达式的值。

3.2 DATE

DATE函数是MySQL中的一个函数,用于从日期或日期时间值中提取日期部分。

这是DATE函数的基本语法:

DATE(expression)

在这个语法中,expression是一个日期或日期时间表达式。


Comment