我想根据国家、合作伙伴和零售商来计算总点击量和唯一点击量。我已经达到了预期的结果,但我认为这不是一个最佳解决方案,对于更长的数据集,这将需要更长的时间。如何改进此查询?这是我的测试表,设计的查询和预期的输出:
"country_id","partner","retailer","id_customer","id_clickout"
"1","A","B","100","XX"
"1","A","B","100","XX"
"2","A","B","100","XX"
"2","A","B","100","GG"
"2","A","B","100","XX"
"2","A","B","101","XX"
DROP TABLE IF EXISTS x;
CREATE TEMPORARY TABLE x AS
SELECT test1.country_id, test1.partner,test1.retailer, test1.id_customer,
SUM(CASE WHEN test1.id_clickout IS NULL THEN 0 ELSE 1 END) AS clicks,
CASE WHEN test1.id_clickout IS NULL THEN 0 ELSE 1 END AS unique_clicks
FROM test1
GROUP BY 1,2,3,4
;
SELECT country_id,partner,retailer, SUM(clicks), SUM(unique_clicks)
FROM x
GROUP BY 1,2,3
输出:
"country_id","partner","retailer","SUM(clicks)","SUM(unique_clicks)"
"1","A","B","2","1"
"2","A","B","4","2"
这是DDL和输入数据:
CREATE TABLE test (
country_id INT(11) DEFAULT NULL,
partner VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
retailer VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
id_customer BIGINT(20) DEFAULT NULL,
id_clickout VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test VALUES(1,'A','B','100','XX'),(1,'A','B','100','XX'),
(2,'A','B','100','XX'),(2,'A','B','100','GG'),
(2,'A','B','100','XX'),(2,'A','B','101','xx')
SELECT
country_id,
partner,
retailer,
COUNT(id_clickout) AS clicks,
COUNT(DISTINCT CASE WHEN id_clickout IS NOT NULL THEN id_customer END) AS unique_clicks
FROM
test1
GROUP BY
1,2,3
;
COUNT(a_field)
不会计算任何NULL
值。
因此,COUNT(id_clickout)
将只计算它是NOT NULL
的次数。
同样,unique_clicks
中的CASE WHEN
语句只为单击的记录返回id_customer
,否则返回NULL
。这意味着COUNT(DISTINCT CASE)
只计算不同的客户,并且只在他们点击时计算。
编辑:
我刚刚意识到,它可能比这更简单。。。
SELECT
country_id,
partner,
retailer,
COUNT(*) AS clicks,
COUNT(DISTINCT id_customer) AS unique_clicks
FROM
test1
WHERe
id_clickout IS NOT NULL
GROUP BY
1,2,3
;
结果中唯一的实质性差异是,以前与0 clicks
一起出现的任何country_id, partner, retailed
现在都不会出现在结果中。
但是,如果在country_id, partner, retailed, id_clickout, id_customer
或country_id, partner, retailed, id_customer, id_clickout
上使用INDEX,则此查询应该会快得多。
我想这就是你想要的:
SELECT country_id,partner,retailer,COUNT(retailer) as `sum(clicks)`,count(distinct id_clickout) as `SUM(unique_clicks)`
FROM test1
GROUP BY country_id,partner,retailer
结果:
COUNTRY_ID PARTNER RETAILER SUM(CLICKS) SUM(UNIQUE_CLICKS)
1 A B 2 1
2 A B 4 2
请参阅SQL Fiddle中的结果。