mysql - 我可以在 SQL 中组合 2 个级别的计数吗？

Question

可能的数据是这样的:

user    RO  retweetID
jim     o   (null)
jim     o   (null)
jim     r   r8
bill    o   (null)
bill    r   r3
fred    o   (null)
fred    r   r6
fred    r   r6
fred    r   r1

我想计算 o、r 和 avgercount(r 的总数/不同 r 的数量)所以我应该得到:

user        ocount rcount avgercount
jim         2      1      1
bill        1      1      1
fred        1      3      1.5

我在 SQLFiddle 上卡在了开头.非常感谢任何帮助。

编辑:澄清:avgercount =(r 的总数/不同 r 的数量)对于 Fred，他有三个转推:r6、r6、r1，但只有两个不同，avgercount = 3/2。

Answer 1

只需使用条件聚合来获取基数:

select user,
       sum(case when ro = 'o' then 1 else 0 end) as o_cnt,
       sum(case when ro = 'r' then 1 else 0 end) as r_cnt,
       avg(case when ro = 'r' then 1.0 else 0.0 end) as avg_r,
       sum(case when ro = 'r' then 1.0 else 0 end) / count(distinct case when ro = 'r' then retweetID end) as retweet_ratio
from t
group by user;

这计算“r”的平均值。不清楚你在问题中的计算是什么。

在 MySQL 中，这可以缩短为:

select user,
       sum( ro = 'o' ) as o_cnt,
       sum( ro = 'r' ) as r_cnt,
       sum( ro = 'r' ) / count(distinct case when ro = 'r' then retweetID end) as retweetid_ratio
from t
group by user;