无法使用DISTINCT和windows函数获取SQL查询

Question

由于DISTINCT关键字，我无法使其工作

SELECT 
ROW_NUMBER() OVER() AS tweet_bucket,
DISTINCT COUNT(user_id) AS users_num
FROM
tweets
WHERE 
tweet_date >= '2022-01-01 00:00:00' 
GROUP BY 
user_id

我知道如果我删除窗口功能，只把它放为：

SELECT DISTINCT 
COUNT(user_id) AS users_num
FROM 
tweets
WHERE 
tweet_date >= '2022-01-01 00:00:00' 
GROUP BY 
user_id

它工作于

我无法删除windows函数，因为我需要使用ROW_NUMBER()获取row_id

真的很挣扎，有没有用同样的逻辑和不同的？

这是我的数据：

CREATE TABLE tweets 
(
tweet_id INT,
user_id INT,
msg VARCHAR(500),
tweet_date DATETIME
);

DROP TABLE tweets;

INSERT INTO tweets 
VALUES (214252, 111, "Am considering taking Tesla private at $420. Funding secured", '2021-12-30 00:00:00');
INSERT INTO tweets 
VALUES (739252, 111, "Despite the constant negative press covfefe", '2022-01-01 00:00:00');
INSERT INTO tweets 
VALUES (846402, 111, "Following @NickSinghTech on Twitter changed my life!", '2022-02-14 00:00:00');
INSERT INTO tweets 
VALUES (241425, 254, "If the salary is so competitive why won’t you tell me what it is?", '2022-03-01 00:00:00');
INSERT INTO tweets 
VALUES (231574, 148, "I no longer have a manager. I can't be managed", '2022-03-01 00:00:00');

SELECT * FROM tweets;
SELECT DISTINCT 
COUNT(user_id) AS users_num,
ROW_NUMBER() OVER() AS tweet_bucket
FROM 
tweets
WHERE 
tweet_date >= '2022-01-01 00:00:00' 
GROUP BY 
user_id

Answer 1

您可以在Oracle PL/SQL中使用它，也许您应该尝试OVER(ORDER BY user_id)。

sql

SELECT DISTINCT COUNT(user_id) AS users_num, 
ROW_NUMBER() OVER(ORDER BY user_id) AS tweet_bucket
FROM tweets
WHERE tweet_date >= to_date('2022-01-01','YYYY-MM-DD')
GROUP BY user_id
order by tweet_bucket

希望这篇文章/答案也有助于更好地理解：Oracle中的OVER子句

Answer 2

我用CTE 得到了正确的答案

WITH HistogramTweets AS   (   SELECT   DISTINCT COUNT(user_id) AS
users_num   FROM tweets   WHERE tweet_date >= '2022-01-01 00:00:00'  
GROUP BY user_id ) SELECT ROW_NUMBER() OVER() AS tweet_bucket,
users_num FROM HistogramTweets