【背景】 某内容社区每月评估广告活动（Campaign）的投放效果。请基于用户表与事件流数据，统计每个活动在 2024 年 2 月的“注册转化（signup）”效果，并与 2024 年 1 月、2023 年 2 月对比；同时给出 2023 年各区域（华北、华东、华南）的注册占比，以及 2024 年 2 月“注册数最多的渠道（channel）”。数据来自活动表、用户表与事件表。 【原始表】 Campaigns（活动）表 campaign_id INT 主键：活动唯一标识 campaign_name VARCHAR：活动名称 category VARCHAR：活动类别（如 品宣拉新召回） Users（用户）表 user_id INT 主键 region ENUM('华北','华东','华南') device ENUM('app','web','mini') Events（事件流）表 event_id INT 主键 user_id INT 外键 → Users.user_id campaign_id INT 外键 → Campaigns.campaign_id event_type ENUM('view','click','signup')：事件类型 channel ENUM('app','web','mini')：触达渠道 event_time DATETIME：事件时间 【要求】 对每个活动输出下列字段，并按 campaign_id 升序（若并列再按 campaign_name 升序）： campaign_id：活动ID campaign_name：活动名称 conv_2023_02：2023-02 signup 数 conv_2024_02：2024-02 signup 数 conv_2024_01：2024-01 signup 数 yoy_delta：同比增量 = conv_2024_02 − conv_2023_02 mom_delta：环比增量 = conv_2024_02 − conv_2024_01 north_pct_2023 south_pct_2023 east_pct_2023：2023 年各区域注册占比（%），保留两位小数；若 2023 总注册数为 0，则三项均为 0.00 avg_click_to_signup_min_2024_02：2024-02 从“最近一次不晚于注册时间的 click”到“signup”的平均分钟数（同一用户同一活动，若无点击则该注册不计入平均），保留两位小数 top_channel_2024_02：2024-02 注册数最多的渠道；若并列，按 channel 字典序（app 【示例输入】 Campaigns +-------------+---------------+----------+ campaign_id campaign_name category +-------------+---------------+----------+ 1 Spring Sale 拉新 2 Summer Boost 拉新 +-------------+---------------+----------+ Users +---------+--------+-------+ user_id region device +---------+--------+-------+ 1 华北 app 2 华东 web 3 华南 mini +---------+--------+-------+ Events +----------+---------+-------------+------------+--------+---------------------+ event_id user_id campaign_id event_type channel event_time +----------+---------+-------------+------------+--------+---------------------+ 101 1 1 click app 2023-02-10 11:00:00 102 1 1 signup app 2023-02-10 11:10:00 103 2 1 click web 2023-02-12 18:05:00 104 2 1 signup web 2023-02-12 18:20:00 105 2 2 click web 2023-02-15 12:10:00 106 2 2 signup web 2023-02-15 12:30:00 107 3 1 click mini 2024-01-20 18:40:00 108 3 1 signup mini 2024-01-20 19:00:00 201 1 1 click app 2024-02-12 11:10:00 202 1 1 signup app 2024-02-12 11:30:00 203 2 1 click web 2024-02-18 18:15:00 204 2 1 signup web 2024-02-18 18:30:00 205 1 1 click app 2024-02-28 12:40:00 206 1 1 signup app 2024-02-28 12:50:00 207 2 2 click web 2024-02-03 12:05:00 208 2 2 signup web 2024-02-03 12:20:00 209 3 2 click mini 2024-02-06 17:50:00 210 3 2 signup mini 2024-02-06 18:10:00 +----------+---------+-------------+------------+--------+---------------------+ 【示例输出】 +-------------+---------------+------------+------------+------------+----------+----------+------------------+------------------+------------------+------------------------------+---------------------+ campaign_id campaign_name conv_2023_02 conv_2024_02 conv_2024_01 yoy_delta mom_delta north_pct_2023 south_pct_2023 east_pct_2023 avg_click_to_signup_min_2024_02 top_channel_2024_02 +-------------+---------------+--------------+--------------+--------------+----------+----------+------------------+------------------+------------------+------------------------------+---------------------+ 1 Spring Sale 2 3 1 1 2 50.00 0.00 50.00 15.00 app 2 Summer Boost 1 2 0 1 2 0.00 0.00 100.00 17.50 mini +-------------+---------------+--------------+--------------+--------------+----------+----------+------------------+------------------+------------------+------------------------------+---------------------+
示例1

输入

DROP TABLE IF EXISTS Events;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Campaigns;

CREATE TABLE Campaigns (
  campaign_id   INT PRIMARY KEY,
  campaign_name VARCHAR(100),
  category      VARCHAR(50)
);

CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  region  ENUM('华北','华东','华南'),
  device  ENUM('app','web','mini')
);

CREATE TABLE Events (
  event_id    INT PRIMARY KEY,
  user_id     INT,
  campaign_id INT,
  event_type  ENUM('view','click','signup'),
  channel     ENUM('app','web','mini'),
  event_time  DATETIME
);

INSERT INTO Campaigns VALUES
(1,'Spring Sale','拉新'),
(2,'Summer Boost','拉新');

INSERT INTO Users VALUES
(1,'华北','app'),
(2,'华东','web'),
(3,'华南','mini');

INSERT INTO Events VALUES
(101,1,1,'click','app','2023-02-10 11:00:00'),
(102,1,1,'signup','app','2023-02-10 11:10:00'),
(103,2,1,'click','web','2023-02-12 18:05:00'),
(104,2,1,'signup','web','2023-02-12 18:20:00'),
(105,2,2,'click','web','2023-02-15 12:10:00'),
(106,2,2,'signup','web','2023-02-15 12:30:00'),
(107,3,1,'click','mini','2024-01-20 18:40:00'),
(108,3,1,'signup','mini','2024-01-20 19:00:00'),
(201,1,1,'click','app','2024-02-12 11:10:00'),
(202,1,1,'signup','app','2024-02-12 11:30:00'),
(203,2,1,'click','web','2024-02-18 18:15:00'),
(204,2,1,'signup','web','2024-02-18 18:30:00'),
(205,1,1,'click','app','2024-02-28 12:40:00'),
(206,1,1,'signup','app','2024-02-28 12:50:00'),
(207,2,2,'click','web','2024-02-03 12:05:00'),
(208,2,2,'signup','web','2024-02-03 12:20:00'),
(209,3,2,'click','mini','2024-02-06 17:50:00'),
(210,3,2,'signup','mini','2024-02-06 18:10:00');

输出

campaign_id|campaign_name|conv_2023_02|conv_2024_02|conv_2024_01|yoy_delta|mom_delta|north_pct_2023|south_pct_2023|east_pct_2023|avg_click_to_signup_min_2024_02|top_channel_2024_02
1|Spring Sale|2|3|1|1|2|50.00|0.00|50.00|15.00|app
2|Summer Boost|1|2|0|1|2|0.00|0.00|100.00|17.50|mini
加载中...