mysql表中的原始数据是：
APP EVENTapp1 clkapp1 reqapp2 clkapp1 impapp2 req
APP EVENT
app1 clk
app1 req
app2 clk
app1 imp
app2 req
现在，我想得到以下结果，统计每个应用程序的事件数
APP clk imp reqapp1 1 1 1app2 1 0 1
APP clk imp req
app1 1 1 1
app2 1 0 1
如何编写sql查询来实现这一点？
cuxqih211#
您可以按应用程序分组，然后对事件使用条件计数。
SELECTAPP,COUNT(CASE WHEN EVENT = 'clk' THEN 1 END) AS clk,COUNT(CASE WHEN EVENT = 'imp' THEN 1 END) AS imp,COUNT(CASE WHEN EVENT = 'req' THEN 1 END) AS reqFROM yourtableGROUP BY APPORDER BY APP;
SELECT
APP,
COUNT(CASE WHEN EVENT = 'clk' THEN 1 END) AS clk,
COUNT(CASE WHEN EVENT = 'imp' THEN 1 END) AS imp,
COUNT(CASE WHEN EVENT = 'req' THEN 1 END) AS req
FROM yourtable
GROUP BY APP
ORDER BY APP;
这里是sql fiddle示例
tquggr8v2#
您可以尝试使用聚合函数条件
SELECT APP, SUM(CASE WHEN EVENT ='clk' THEN 1 ELSE 0 END) clk, SUM(CASE WHEN EVENT ='imp' THEN 1 ELSE 0 END) imp, SUM(CASE WHEN EVENT ='req' THEN 1 ELSE 0 END) reqFROM TGROUP BY APP
SELECT APP,
SUM(CASE WHEN EVENT ='clk' THEN 1 ELSE 0 END) clk,
SUM(CASE WHEN EVENT ='imp' THEN 1 ELSE 0 END) imp,
SUM(CASE WHEN EVENT ='req' THEN 1 ELSE 0 END) req
FROM T
toiithl63#
mysql提供了条件聚合的简写，因此您可以：
select app, sum(EVENT = 'clk') as clk, sum(EVENT = 'imp') as imp, sum(EVENT = 'req') as reqfrom table tgroup by app;
select app,
sum(EVENT = 'clk') as clk,
sum(EVENT = 'imp') as imp,
sum(EVENT = 'req') as req
from table t
group by app;
这把小提琴显示它在工作
3条答案
按热度按时间cuxqih211#
您可以按应用程序分组，然后对事件使用条件计数。
这里是sql fiddle示例
tquggr8v2#
您可以尝试使用聚合函数条件
toiithl63#
mysql提供了条件聚合的简写，因此您可以：
这把小提琴显示它在工作