我想做一个2019年1月每个用户 comments 数的柱状图(包括一次没有 comments 的)
我正在使用的表如下所示:
| id | Name |
|---|---|
| 1 | Jose |
| 2 | Pedro |
| 3 | Juan |
| 4 | Sofia |
| user_id | Comment | Date |
|---|---|---|
| 1 | Hello | 2018-10-02 11:00:03 |
| 3 | Didn't Like it | 2018-06-02 11:00:03 |
| 1 | Not so bad | 2018-10-22 11:00:03 |
| 2 | Trash | 2018-7-21 11:00:03 |
我想我把它复杂化了.但我的try 是:
#Here I'm counting how much comments are per person that have commented.
CREATE TABLE aux AS
SELECT user_id, COUNT(user_id)
FROM Undostres
GROUP BY user_id;
#With the following code, I end up with a table with the missing values (ids that haven't commented)
CREATE TABLE Test AS
SELECT DISTINCT user_id +1
FROM aux
WHERE user_id + 1 NOT IN (SELECT DISTINCT user_id FROM aux);
ALTER TABLE Test RENAME COLUMN user_id +1 TO ;
INSERT INTO Undostres (user_id)
SELECT user_id FROM Test;
当我try 用其他名称重命名user\u id+1时，它返回一个错误.所以我不能继续.
任何建议都会很好！