我正在尝试从同一个表中同时执行 count(distinct(keyword)) 和 count(keyword) 。这是两个查询。
查询 1
SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,users t4
WHERE t4.user_id='18839785'
AND t1.id=t2.id AND t2.id=t3.id AND t3.user_id=t4.user_id AND t2.sent='Pos';
count
-------
251
(1 row)
查询 2
SELECT t1.keyword,t1.count(keyword) FROM keyword t1, sent t2,tweets t3,users t4
WHERE t4.user_id='18839785' AND t3.id=t4.user_id AND t2.id=t3.id
AND t2.id=t1.id AND t2.sent='Pos' GROUP BY t1.keyword order by count(keyword) desc ;
nlp_keyword_name | count
------------------+-------
happy | 5
people | 4
handsome | 4
glad | 3
forward | 3
wishes | 3
proud | 2
(7 rows)
查询 3
SELECT distinct_drivers,positive_drivers,countt
from (
SELECT t1.count(distinct(keyword)) as distinct_drivers ,
t1.nlp_keyword_name as P_drivers,
t1.count(nlp_keyword_name) as countt
FROM keyword t1 LEFT JOIN sent t2 on t1.id=t2.id
LEFT JOIN tweets t3 on t3.id=t2.id
LEFT JOIN users t4 on t4.user_id=t3.user_id
WHERE (t4.user_id)=('18839785') AND t2.sent='Pos' GROUP BY t1.keyword order by countt desc) T;
但我得到我的输出
distinct_drivers | positive_drivers | countt
------------------+------------------+--------
1 | happy | 5
1 | people | 4
1 | handsome | 4
1 | glad | 3
1 | forward | 3
在执行 query-3 之后,我的输出必须低于我不会那样的方式。
**desired_output:**
distinct_drivers | positive_drivers | countt
------------------+------------------+--------
251 | happy | 5
251 | people | 4
251 | handsome | 4
251 | glad | 3
251 | forward | 3
任何人都可以帮助我实现所需的输出。我会很感激。我在日期间隔内完成的滚动总和/计数/平均值的资源
1 个回答