mysql - 如何计算每个类别的作业的唯一点击次数

Question

我将归因于不同类别的链接性能存储在 sent 表中，如下所示...

CREATE TABLE sent (
  customer_id INT,
  jobId INT,
  category VARCHAR(100),
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

我正在尝试创建一个查询，该查询可以显示 Y 轴上的 customer_id 以及 X 轴上的不同类别。使这变得复杂的是，我只想为每个唯一的作业 ID 计算一次点击。我已经尝试使用下面的查询，但这只是一个类别。我要搜索的类别是固定的(总共大约 10 个)，我想知道如何以最有效的方式进行搜索。该表可能有数百万条记录。

SELECT customer_id, count(customer_id) as unique_clicks_discovery
FROM (
  SELECT customer_id, jobId, COUNT(*) as discovery_clicks_unique
  FROM sent
  WHERE category = 'discovery'
  GROUP BY customer_id, jobId
) as discovery_click_roundup
GROUP BY customer_id

编辑:这是预期的结果，请引用fiddle查看数据。

ACCOUNT ID    |   DISCOVER UNIQUE CLICKS  |  PURCHASE UNIQUE CLICKS
--------------------------------------------------------------------
101           |   3                       |  3
102           |   1                       |  1
103           |   1                       |  1
104           |   1                       |  2

编辑 2:使用示例数据:

INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 202, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 202, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 202, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');

DB Fiddle (包括示例数据)

Answer 1

您可以在此处使用条件聚合:

SELECT customer_id
, COUNT(DISTINCT CASE category WHEN 'discovery' THEN jobId END) AS unique_clicks_discovery
, COUNT(DISTINCT CASE category WHEN 'purchase'  THEN jobId END) AS unique_clicks_purchase
FROM sent
GROUP BY customer_id;

您必须为每个类别附加一个 COUNT(..) 行。

结果:

| customer_id | unique_clicks_discovery | unique_clicks_purchase |
| ----------- | ----------------------- | ---------------------- |
| 101         | 3                       | 3                      |
| 102         | 1                       | 1                      |
| 103         | 1                       | 1                      |
| 104         | 1                       | 2                      |

View on DB Fiddle