SQL按行划分数据

Question

我的下表名为

表:活动

userid    appid    type
1         a        imp
2         a        imp
2         a        click
3         a        imp
4         a        imp
4         a        click
5         b        imp
5         b        click

我正在计算每个appid的点击率.在这种情况下，我们将点击-通过定义为(点击次数)/(印象次数)。我编写了以下SQL：

SELECT appid, type, count(*) from activity group by appid, type

得到了以下结果：

输出：

appid    type       count(*)
a        click      2
a        imp        4
b        click      1
b        imp        1

下一步是逐行除法。最后，我希望做到以下几点：

目标：

appid    click-through
a        .5                        # 2/4 = .5
b        1                         # 1/1 = 1

这是如何实现的？理想情况下，我希望在一个查询中完成这个任务，这是可能的吗？

Answer 1

您可以使用条件聚合来完成此操作：

   select appid
        , SUM(CASE WHEN type = 'click' THEN 1 END)*1.0
        / SUM(CASE WHEN type = 'imp' THEN 1 END) AS click_through
    from activity
    group by appid

演示：SQL Fiddle

如果使用MySQL，您可以通过以下方法进一步简化：

   select appid
        , SUM(type = 'click')*1.0
        / SUM(type = 'imp') AS click_through
    from activity
    group by appid

Answer 2

只需计算子查询中的印象和单击，并将它们连接在一起：

select appid, 
num_impressions, 
num_clicks, 
cast(num_clicks as float) / num_impressions as ctr
from(
    select appid, count(1) as num_impressions
    from activity
    where type = 'imp'
    group by appid
)a
join(
    select appid, count(1) as num_clicks
    from activity
    where type = 'click'
    group by appid
)b
on (a.appid = b.appid);

注意num_clicks在ctr中的类型转换，以避免整数除法。

Answer 3

您可以使用子查询来完成这一任务，因为您正在使用不同的标准处理两个不同的聚合：

SELECT d1.appid, (
   SELECT count(*) 
   FROM activity d2
   WHERE d2.appid = d1.appid
      d2.type = 'click'
) / (
   SELECT count(*)
   FROM activity d3
   WHERE d3.appid = d1.appid
      d3.type = 'imp'
) AS click_through
FROM activity d1;