创建一周内活动天数的分布。SQL

CREATE TABLE activities (
    activity_id INT PRIMARY KEY,
    activity_date DATE NOT NULL
);

SELECT 
    DAYNAME(activity_date) AS weekday, -- 获取星期几的名称
    COUNT(*) AS activity_count -- 统计每天的活动数量
FROM 
    activities
WHERE 
    activity_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() -- 限定查询范围为一周内
GROUP BY 
    weekday; -- 按星期几分组

SELECT 
    DAYNAME(activity_date) AS weekday,
    COUNT(*) AS activity_count
FROM 
    activities
WHERE 
    activity_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()
    AND DAYOFWEEK(activity_date) BETWEEN 2 AND 6 -- 只计算周一到周五
GROUP BY 
    weekday;