mysql行和

ff29svar 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(265)

我有下表

+--------+----+----+----+----+----+----+
| userid | z0 | z1 | z2 | z3 | z4 | z5 |
+--------+----+----+----+----+----+----+
| 4711   |  0 |  1 |  1 |  0 |  0 |  0 |
| 4712   |  1 |  1 |  8 |  0 |  5 |  0 |
| 4713   |  3 |  5 |  1 |  0 |  0 |  0 |
| 4714   |  0 |  4 |  2 |  0 | 11 |  0 |
+--------+----+----+----+----+----+----+

此表记录特定时间段内发生的事件数。也就是说，在给定的时间范围内（0-4、4-8、8-12、12-16、16-20、20-24点钟），从某个用户id到某个网站的页面浏览量。
我想使用sql计算以下内容：
有多少时间段（每个用户）至少有一次访问？
有多少时间段（每个用户）至少有k次访问（k=5,10）？
预期结果：

+--------+--------+--------+---------+
| userid | visit1 | visit5 | visit10 |
+--------+--------+--------+---------+
| 4711   |     2  |      0 |       0 |
| 4712   |     4  |      2 |       0 |
| 4713   |     3  |      1 |       0 |
| 4714   |     3  |      1 |       1 |
+--------+--------+--------+---------+

到目前为止我所拥有的：
对于“有多少时间段（每个用户）至少有一次访问？”的问题，我尝试过类似的方法，但没有成功：

SELECT 
SUM(
  CASE WHEN z0>0 THEN 1 ELSE 0 END 
  CASE WHEN z1>0 THEN 1 ELSE 0 END 
  CASE WHEN z2>0 THEN 1 ELSE 0 END 
  CASE WHEN z3>0 THEN 1 ELSE 0 END 
  CASE WHEN z4>0 THEN 1 ELSE 0 END 
  CASE WHEN z5>0 THEN 1 ELSE 0 END 
) AS visit1
FROM timetable ;

sql mysql

来源：https://stackoverflow.com/questions/48079161/mysql-rowsum-over-columns-with-condition

3条答案

按热度按时间

6tqwzwtp1#

正如草莓提到的，最好修改你的模式设计。但是，如果您只想获得所需的结果，可以尝试以下方法（不是最佳解决方案）：

SELECT  userid,
    z0 + z1 + z2 + z3 + z4 + z5 as visit1,
    z0_5 + z1_5 + z2_5 + z3_5 + z4_5 + z5_5 as visit5,
    z0_10 + z1_10 + z2_10 + z3_10 + z4_10 + z5_10 as visit10

FROM (
   SELECT 
   userid,

   CASE WHEN z0>0 THEN 1 ELSE 0 END z0,
   CASE WHEN z1>0 THEN 1 ELSE 0 END z1,
   CASE WHEN z2>0 THEN 1 ELSE 0 END z2,
   CASE WHEN z3>0 THEN 1 ELSE 0 END z3,
   CASE WHEN z4>0 THEN 1 ELSE 0 END z4,
   CASE WHEN z5>0 THEN 1 ELSE 0 END z5,

   CASE WHEN z0>4 THEN 1 ELSE 0 END z0_5,
   CASE WHEN z1>4 THEN 1 ELSE 0 END z1_5,
   CASE WHEN z2>4 THEN 1 ELSE 0 END z2_5,
   CASE WHEN z3>4 THEN 1 ELSE 0 END z3_5,
   CASE WHEN z4>4 THEN 1 ELSE 0 END z4_5,
   CASE WHEN z5>4 THEN 1 ELSE 0 END z5_5,

   CASE WHEN z0>9 THEN 1 ELSE 0 END z0_10,
   CASE WHEN z1>9 THEN 1 ELSE 0 END z1_10,
   CASE WHEN z2>9 THEN 1 ELSE 0 END z2_10,
   CASE WHEN z3>9 THEN 1 ELSE 0 END z3_10,
   CASE WHEN z4>9 THEN 1 ELSE 0 END z4_10,
   CASE WHEN z5>9 THEN 1 ELSE 0 END z5_10

   FROM timetable 
   GROUP BY userid
) as test

演示

赞(0）回复(0）举报 2021-06-21

lyfkaqu12#

基于你现有的结构，如果你能调整到一个更好的格式将使事情变得更容易。。。但不能抵挡，试试看

select
      userid,
        case when z0 >= 1 then 1 else 0 end
      + case when z1 >= 1 then 1 else 0 end
      + case when z2 >= 1 then 1 else 0 end
      + case when z3 >= 1 then 1 else 0 end
      + case when z4 >= 1 then 1 else 0 end
      + case when z5 >= 1 then 1 else 0 end as Visit1Group,
        case when z0 >= 5 then 1 else 0 end
      + case when z1 >= 5 then 1 else 0 end
      + case when z2 >= 5 then 1 else 0 end
      + case when z3 >= 5 then 1 else 0 end
      + case when z4 >= 5 then 1 else 0 end
      + case when z5 >= 5 then 1 else 0 end as Visit5Group,
        case when z0 >= 10 then 1 else 0 end
      + case when z1 >= 10 then 1 else 0 end
      + case when z2 >= 10 then 1 else 0 end
      + case when z3 >= 10 then 1 else 0 end
      + case when z4 >= 10 then 1 else 0 end
      + case when z5 >= 10 then 1 else 0 end as Visit10Group
   FROM 
      timetable ;

赞(0）回复(0）举报 2021-06-21

s4n0splo3#

修复架构后，有效查询可能如下所示：

SELECT userid
     , SUM(k>=1)  visit1
     , SUM(k>=5)  visit5
     , SUM(k>=10) visit10 
  FROM my_table 
 GROUP 
    BY userid;

赞(0）回复(0）举报 2021-06-21

我来回答

mysql行和

3条答案

相关问题

热门标签

最新问答