我在一个专栏中有情绪分析的结果。我想展示每个数字代表的内容和计数,如下所示:
Neg | Neutral | Pos
---------+-----+-----
30 | 55 | 100
我在网上找到了很多不同的方法,但都没有用。db2语法似乎经常更改,我发现的所有文档在我当前的bluemix帐户上都不起作用。
我能得到的最接近的是联合:
SELECT COUNT(*) as NEGATIVE FROM C7 WHERE SENTIMENT = 1
union
SELECT COUNT(*) as NEUTRAL FROM C7 WHERE SENTIMENT = 2
union
SELECT COUNT(*) as Pos FROM C7 WHERE SENTIMENT = 3
然而,这给了我:
NEGATIVE
--------
30
55
100
如何并排组合每个查询的结果?
我在DB2中尝试了post-Pivoting的答案,但出现了错误:
SELECT
C7.SENTIMENT,
COUNT(CASE WHEN C7.SENTIMENT = 1 THEN Value END) AS NEGATIVE,
COUNT(CASE WHEN C7.SENTIMENT = 2 THEN Value END) AS NEUTRAL,
COUNT(CASE WHEN C7.SENTIMENT = 3 THEN Value END) AS POSITIVE,
FROM C7
GROUP BY C7.SENTIMENT
SQL0206N:SQL0206N“值”在使用它的上下文中无效。SQLSTATE=42703
SELECT DISTINCT
C7.SENTIMENT,
(SELECT value FROM C7 WHERE C7.SENTIMENT = 1) AS VERY_NEGATIVE,
(SELECT value FROM C7 WHERE C7.SENTIMENT = 2) AS NEGATIVE,
(SELECT value FROM C7 WHERE C7.SENTIMENT = 3) AS NEUTRAL
FROM C7