DAYOFWEEK(date) 函数详解

DAYOFWEEK(date) 函数详解

DAYOFWEEK(date) 是一个用于从日期或日期时间表达式中提取星期几数字表示的日期和时间函数。它在数据分析、报告生成、时间序列处理、事件调度以及各种需要基于星期几进行分类和计算的场景中非常实用。通过 DAYOFWEEK 函数，用户可以轻松地获取日期对应的星期几数字，从而简化基于星期几的分析和分类工作。

1. DAYOFWEEK(date) 函数的基本语法

DAYOFWEEK(date) 函数用于从给定的日期或日期时间表达式中提取星期几的数字表示，返回一个整数值，表示该日期对应的星期几。

语法：

DAYOFWEEK(date)

• date：需要提取星期几数字的日期或日期时间表达式。可以是 DATE、DATETIME、TIMESTAMP 类型的列、日期常量、日期函数的返回值，或者任何返回有效日期值的表达式。

返回值：

• 返回提取后的星期几数字，类型为 INT。

• 返回值范围通常为 1 到 7，具体取决于数据库系统的实现和配置。

  • 在 MySQL 中，1 代表 星期日，2 代表 星期一，依此类推，直到 7 代表 星期六。
  • 在 SQL Server 中，返回值取决于 @@DATEFIRST 设置，默认情况下 1 代表 星期日，2 代表 星期一，依此类推，直到 7 代表 星期六。
  • 在 PostgreSQL 中，没有直接的 DAYOFWEEK 函数，但可以使用 EXTRACT(DOW FROM date)，其返回值范围为 0（星期日）到 6（星期六）。
• 如果 date 为 NULL，函数返回 NULL。
• 如果 date 无法转换为有效的日期，函数可能返回错误，具体行为取决于数据库实现。

示例：

1. 从 DATE 字段中提取星期几数字：

   假设有一个名为 employees 的表，包含 employee_id、name 和 hire_date 列。

   SELECT 
       employee_id, 
       name, 
       hire_date, 
       DAYOFWEEK(hire_date) AS hire_day_number
   FROM 
       employees;

   执行结果为：

   employee_id | name        | hire_date  | hire_day_number
   ------------|-------------|------------|-----------------
   1           | John Doe    | 2022-01-15 | 7
   2           | Anna Smith  | 2023-05-20 | 7
   3           | Bob Johnson | 2024-06-01 | 7

   解释：DAYOFWEEK(hire_date) 提取了 hire_date 字段对应的星期几数字。假设这些日期均为星期六，故返回 7。

2. 从字符串中提取星期几数字：

   SELECT DAYOFWEEK('2024-09-25') AS extracted_day_number;

   执行结果为：

   extracted_day_number
   ---------------------
   4

   解释：假设 2024-09-25 是星期三，DAYOFWEEK 返回 4。在 MySQL 中，1 代表星期日，因此 4 代表星期三。

3. 处理包含时间部分的列：

   假设有一个名为 orders 的表，包含 order_id 和 order_datetime 列。

   SELECT 
       order_id, 
       order_datetime, 
       DAYOFWEEK(order_datetime) AS order_day_number
   FROM 
       orders;

   执行结果为：

   order_id | order_datetime       | order_day_number
   ---------|----------------------|------------------
   1001     | 2024-09-25 14:30:45  | 4
   1002     | 2024-09-26 09:15:00  | 5
   1003     | 2024-09-27 18:45:30  | 6

   解释：DAYOFWEEK(order_datetime) 提取了 order_datetime 字段对应的星期几数字。假设 2024-09-25 是星期三，2024-09-26 是星期四，2024-09-27 是星期五，分别返回 4、5 和 6。

2. 使用场景

2.1 数据分类与分组

在数据分析中，基于星期几对数据进行分类和分组是常见需求。使用 DAYOFWEEK 函数可以方便地按星期几进行分组统计。

示例：

按星期几统计每周的销售额：

SELECT 
    DAYOFWEEK(sale_date) AS sale_day_number, 
    COUNT(*) AS total_sales, 
    SUM(amount) AS total_amount
FROM 
    sales
GROUP BY 
    DAYOFWEEK(sale_date)
ORDER BY 
    sale_day_number;

2.2 周期性报告生成

在生成周期性报告（如每周报告）时，DAYOFWEEK 函数可以帮助标记和分析不同星期几的数据表现。

示例：

生成每周各天的用户注册数量报告：

SELECT 
    DAYOFWEEK(registration_date) AS registration_day_number, 
    COUNT(*) AS registrations
FROM 
    users
GROUP BY 
    DAYOFWEEK(registration_date)
ORDER BY 
    registration_day_number;

2.3 条件过滤与查询

在 WHERE 子句中使用 DAYOFWEEK 函数，可以基于星期几动态筛选记录，如查找特定星期几的事件或活动。

示例：

查找所有在星期一创建的订单：

SELECT 
    order_id, 
    order_date
FROM 
    orders
WHERE 
    DAYOFWEEK(order_date) = 2; -- 在 MySQL 中，2 代表星期一

2.4 用户行为分析

分析用户在不同星期几的行为模式，如访问量、购买量等，有助于优化营销策略和资源分配。

示例：

分析每周各天的网页访问量：

SELECT 
    DAYOFWEEK(access_time) AS access_day_number, 
    COUNT(*) AS access_count
FROM 
    web_logs
GROUP BY 
    DAYOFWEEK(access_time)
ORDER BY 
    access_day_number;

2.5 数据记录与审计

在记录数据插入或更新时，使用 DAYOFWEEK 函数可以自动标记操作发生的星期几，便于后续的审计和追踪。

示例：

将当前操作的星期几数字插入到 audit_logs 表中：

INSERT INTO audit_logs (log_id, action, log_day_number)
VALUES (1001, 'UPDATE', DAYOFWEEK(NOW()));

3. DAYOFWEEK(date) 函数与其他日期函数的对比

DAYOFWEEK(date) 函数在不同的数据库系统中可能与其他日期和时间函数具有相似或不同的功能。了解它们之间的区别有助于选择合适的函数来满足特定需求。

• DAY(date)：功能与 DAYOFMONTH(date) 相同，用于返回日期的天数部分。某些数据库系统（如 MySQL）支持此函数。

  示例：

  SELECT DAY('2024-09-25') AS day_part;

  返回 25。

• EXTRACT(DOW FROM date)（PostgreSQL）：符合标准 SQL 的函数，用于从日期中提取星期几的数字表示。

  示例：

  SELECT EXTRACT(DOW FROM DATE '2024-09-25') AS day_of_week;

  返回 3（在 PostgreSQL 中，0 代表星期日，6 代表星期六）。

• DATEPART(dw, date)（SQL Server）：用于从日期中提取星期几的数字表示。

  示例：

  SELECT DATEPART(dw, '2024-09-25') AS day_part;

  返回 4（假设 @@DATEFIRST 为默认值 7，即星期日为 1）。

• WEEKDAY(date)（MySQL）：返回日期对应的星期几数字，范围为 0（星期一）到 6（星期日）。

  示例：

  SELECT WEEKDAY('2024-09-25') AS weekday_num;

  返回 2（星期三）。

• DAYNAME(date)：用于返回日期的星期几名称，而非数字表示。

  示例：

  SELECT DAYNAME('2024-09-25') AS day_name;

  返回 'Wednesday'。

总结比较：

函数名称	功能	返回值类型	范围或格式	主要数据库系统
DAYOFWEEK(date)	提取日期的星期几数字	INT	1-7（MySQL: 1=Sun, 7=Sat）	MySQL、MariaDB、GBase 8a等
DAY(date)	提取日期的天数部分	INT	1-31	MySQL、MariaDB等
EXTRACT(DOW FROM date)	提取日期的星期几数字	FLOAT	0-6（PostgreSQL: 0=Sun）	PostgreSQL、MySQL等
DATEPART(dw, date)	提取日期的星期几数字	INT	1-7（SQL Server, 根据 @@DATEFIRST）	SQL Server、Sybase等
WEEKDAY(date)	提取日期的星期几数字	INT	0-6（MySQL: 0=Mon, 6=Sun）	MySQL、MariaDB等
DAYNAME(date)	提取日期的星期几名称	VARCHAR	'Monday' - 'Sunday'	MySQL、MariaDB等

• 选择使用：

  • 获取星期几数字：

    • MySQL：使用 DAYOFWEEK(date) 或 WEEKDAY(date)，根据需要的星期日或星期一为起点的编号。
    • PostgreSQL：使用 EXTRACT(DOW FROM date)。
    • SQL Server：使用 DATEPART(dw, date)，注意 @@DATEFIRST 设置对返回值的影响。
  • 获取天数部分：使用 DAY(date) 或 DAYOFMONTH(date)。
  • 获取星期几名称：使用 DAYNAME(date)。

4. 注意事项

• 参数顺序与起始点：

  • 在 MySQL 中，DAYOFWEEK(date) 返回值 1 代表 星期日，2 代表 星期一，依此类推，直到 7 代表 星期六。
  • 在 SQL Server 中，DATEPART(dw, date) 的返回值取决于 @@DATEFIRST 设置，默认情况下 1 代表 星期日，2 代表 星期一，依此类推，直到 7 代表 星期六。
  • 在 PostgreSQL 中，EXTRACT(DOW FROM date) 返回值 0 代表 星期日，1 代表 星期一，依此类推，直到 6 代表 星期六。
  • 在 MySQL 中，WEEKDAY(date) 返回值 0 代表 星期一，1 代表 星期二，依此类推，直到 6 代表 星期日。

  示例（MySQL）：

  SELECT 
      DAYOFWEEK('2024-09-25') AS day_of_week,
      WEEKDAY('2024-09-25') AS weekday_num;

  假设 2024-09-25 是星期三，结果为：

  day_of_week | weekday_num
  ------------|-------------
  4           | 2

• 时区影响：

  • DAYOFWEEK 函数提取的星期几基于数据库服务器的时区设置。如果涉及跨时区的数据，需确保时区的一致性或进行适当的时区转换。

    示例（MySQL）：

    SELECT 
        DAYOFWEEK(CONVERT_TZ('2024-09-25 14:30:45', 'UTC', 'Asia/Shanghai')) AS shanghai_day_number;

    假设转换后日期仍为星期三，返回 4。

• NULL 值处理：

  • 如果 date 参数为 NULL，函数返回 NULL。

    示例：

    SELECT DAYOFWEEK(NULL) AS day_of_week;

    返回 NULL。

• 无效日期处理：

  • 如果 date 无法转换为有效的日期，函数可能返回错误或 NULL，具体取决于数据库系统。

    示例（MySQL）：

    SELECT DAYOFWEEK('invalid-date') AS day_of_week;

    可能返回错误或 NULL。

• 性能考虑：

  • 在处理大量数据时，频繁调用日期函数如 DAYOFWEEK 可能会影响查询性能。应根据具体情况优化查询和数据库设计，例如通过索引优化或减少不必要的函数调用。

• 数据库兼容性：

  • 虽然 DAYOFWEEK 在大多数数据库系统中得到支持，但实现细节可能略有不同。务必参考特定数据库的官方文档以了解详细信息。

    PostgreSQL 示例：

    PostgreSQL 没有直接的 DAYOFWEEK 函数，但可以通过 EXTRACT(DOW FROM date) 或 TO_CHAR 函数实现类似功能。

    SELECT EXTRACT(DOW FROM DATE '2024-09-25') AS day_of_week;
    -- 或者
    SELECT TO_CHAR(DATE '2024-09-25', 'Day') AS day_name;

    返回 3 或 'Wednesday '（注意末尾的空格）。

5. 综合示例

假设我们有一个在线零售平台的数据库，其中包含一个 sales 表，记录了每笔销售的详细信息。我们希望生成一个报告，显示每周各天的总销售额，以及与前一天的销售额比较。

执行：

SELECT 
    DAYOFWEEK(sale_datetime) AS sale_day_number, 
    COUNT(*) AS total_sales, 
    SUM(amount) AS total_amount,
    SUM(amount) - LAG(SUM(amount), 1) OVER (ORDER BY DAYOFWEEK(sale_datetime)) AS difference_from_previous_day
FROM 
    sales
GROUP BY 
    DAYOFWEEK(sale_datetime)
ORDER BY 
    sale_day_number;

执行结果为：

sale_day_number | total_sales | total_amount | difference_from_previous_day
-----------------|-------------|--------------|------------------------------
1                | 100         | 20000        | NULL
2                | 150         | 30000        | 10000
3                | 120         | 25000        | -5000
4                | 130         | 28000        | 3000
5                | 110         | 22000        | -6000
6                | 90          | 18000        | -4000
7                | 80          | 16000        | -2000

解释：

• sale_day_number：提取自 sale_datetime 的星期几数字部分（在 MySQL 中，1 代表星期日，7 代表星期六）。
• total_sales：每周各天的总销售笔数。
• total_amount：每周各天的总销售金额。
• difference_from_previous_day：与前一天的销售金额差异。

该查询通过 DAYOFWEEK(sale_datetime) 提取销售日期的星期几数字，并使用窗口函数 LAG 计算与前一天的销售额差异，从而生成一个详细的销售报告，帮助业务团队分析每周各天的销售趋势和波动。

6. 总结

DAYOFWEEK(date) 是一个基础而强大的日期函数，广泛应用于各种数据处理和分析场景。无论是在数据分类与分组、周期性报告生成，还是在条件过滤和用户行为分析中，DAYOFWEEK 函数都能提供准确和高效的星期几数字提取解决方案。通过使用 DAYOFWEEK(date) 函数，用户可以确保日期数据的动态性和准确性，简化数据处理流程，提升数据分析的准确性和可靠性。

推荐实践：

• 了解数据库特性：不同数据库系统对 DAYOFWEEK 的实现和返回值定义可能不同。务必了解所使用数据库的具体行为，以避免误解和错误。
• 结合其他日期函数使用：在复杂的日期计算中，DAYOFWEEK 可与其他日期函数（如 DATE_ADD、DATEDIFF、DATE_FORMAT 等）结合使用，实现更灵活和精确的日期处理。
• 关注时区设置：确保数据库服务器的时区配置正确，或在需要时使用时区转换函数，以获取符合业务需求的星期几数字。
• 优化性能：在处理大量数据时，合理使用日期函数，避免不必要的计算操作，以优化查询性能。
• 参考官方文档：不同数据库系统可能在函数实现上存在细微差异，务必参考特定数据库的官方文档，确保函数使用的正确性和最佳实践。

通过深入理解和灵活应用 DAYOFWEEK(date) 函数，用户可以在各种星期几数字提取和分析任务中实现更高效和准确的操作，满足多样化的数据需求。

---

GBase 8a 分析型数据库的主要市场是商业分析和商业智能市场。产品主要应用在政府、党委、安全敏感部门、国防、统计、审计、银监、证监等领域，以及电信、金融、电力等拥有海量业务数据的行业。

Q：GBase 8a 能干什么？
A：GBase 8a 能够实现大数据的全数据（结构化数据、半结构化数据和非结构化数据）存储管理和高效分析，为行业大数据应用提供完整的数据库解决方案。

Q：GBase 8a 的水平如何？
A：GBase 8a 能够在百 TB 至 PB 级数据规模下实现数据查询的秒级响应；能够帮助客户节省 50%-90% 存储空间；能够为客户节省 50%-90% 的投资和运维成本；能够对结构化、半结构化和非结构化数据进行统一处理；能够实现千亿级文本条目全文检索的秒级响应；能够提供全过程可视化的数据查询分析及展现工具。