我在理解如何将不同的活动拆分和计算到单独的列中时遇到麻烦。这是开始表:
+------------+---------+
| CustomerID |Activity |
+------------+---------+
| 1 | Click |
| 1 | View |
| 1 | Inquiry |
| 2 | Click |
| 2 | View |
| 3 | Click |
| 3 | Click |
+------------+---------+
我希望能够将其转换为:
+------------+------+-------+---------+
| CustomerID | View | Click | Inquiry |
+------------+------+-------+---------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 0 |
| 3 | 0 | 2 | 0 |
+------------+------+-------+---------+
您可以使用case statement
并sum
喜欢,
select
`CustomerID`,
sum(case when `Activity` = 'View' then 1 else 0 end) `View`,
sum(case when `Activity` = 'Click' then 1 else 0 end) `Click`,
sum(case when `Activity` = 'Inquiry' then 1 else 0 end) `Inquiry`
from `tbl`
group by `CustomerID`
order by `CustomerID`
输出是
CustomerID View Click Inquiry
1 1 1 1
2 1 1 0
3 0 2 0
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句