如何从dealdate列提取DAY/WEEK/MONTH/YEAR并创建summary tour details表

原表：Tour Details #

需求说明 #

创建名为summary tour details的表，需包含以下内容：

• 从dealdate字段提取的DAY、WEEK、MONTH、YEAR信息
• 按SubVenue分组，生成格式为Tours {SubVenue}的分组标识Grp

目标表示例 #

不同数据库的实现代码 #

MySQL #

CREATE TABLE `summary tour details` AS
SELECT
    CONCAT('Tours ', SubVenue) AS Grp,
    DAY(dealdate) AS Day,
    WEEK(dealdate, 1) AS Week, -- 模式1表示周一是每周第一天，可根据需求调整
    MONTH(dealdate) AS Month,
    YEAR(dealdate) AS Year
FROM `Tour Details`
GROUP BY SubVenue, Day, Week, Month, Year;

SQL Server #

CREATE TABLE [summary tour details]
AS
SELECT
    'Tours ' + SubVenue AS Grp,
    DAY(dealdate) AS Day,
    DATEPART(WEEK, dealdate) AS Week,
    MONTH(dealdate) AS Month,
    YEAR(dealdate) AS Year
FROM [Tour Details]
GROUP BY SubVenue, DAY(dealdate), DATEPART(WEEK, dealdate), MONTH(dealdate), YEAR(dealdate);

PostgreSQL #

CREATE TABLE "summary tour details" AS
SELECT
    'Tours ' || "SubVenue" AS "Grp",
    EXTRACT(DAY FROM "dealdate")::INT AS "Day",
    EXTRACT(WEEK FROM "dealdate")::INT AS "Week",
    EXTRACT(MONTH FROM "dealdate")::INT AS "Month",
    EXTRACT(YEAR FROM "dealdate")::INT AS "Year"
FROM "Tour Details"
GROUP BY "SubVenue", "Day", "Week", "Month", "Year";

注意事项 #

1. 若需使用原表中的Weeknumber字段而非从日期提取，只需将代码中的周相关函数替换为Weeknumber即可
2. 分组逻辑为按SubVenue和日期的天/周/月/年维度分组，确保同一分组下的日期维度一致
3. 部分数据库对带空格的表名/字段名需用反引号（MySQL）、方括号（SQL Server）或双引号（PostgreSQL）包裹

内容的提问来源于stack exchange，提问作者Burt Ox