使用分区计算所有状态和特定状态的数量

Question

准备sql查询问题。

CREATE TABLE right_data(RID NUMBER(9,0), STATUS NUMBER(2,0)); 
CREATE TABLE left_data(LID NUMBER(9,0), URL VARCHAR(70 BYTE)); 
CREATE TABLE left_to_right(LID NUMBER(9,0), RID NUMBER(9,0)); 

INSERT INTO right_data (RID, STATUS) VALUES (1,0); 
INSERT INTO right_data (RID, STATUS) VALUES (2,1); 
INSERT INTO right_data (RID, STATUS) VALUES (3,0); 
INSERT INTO right_data (RID, STATUS) VALUES (4,0); 
INSERT INTO right_data (RID, STATUS) VALUES (5,1); 

INSERT INTO left_data (LID, URL) VALUES (1,'data_1'); 
INSERT INTO left_data (LID, URL) VALUES (2,'data_2'); 
INSERT INTO left_data (LID, URL) VALUES (3,'data_3'); 
INSERT INTO left_data (LID, URL) VALUES (4,'data_4'); 
INSERT INTO left_data (LID, URL) VALUES (5,'data_5'); 

INSERT INTO left_to_right (LID, RID) VALUES (1,1); 
INSERT INTO left_to_right (LID, RID) VALUES (1,2); 
INSERT INTO left_to_right (LID, RID) VALUES (2,1); 
INSERT INTO left_to_right (LID, RID) VALUES (1,4); 
INSERT INTO left_to_right (LID, RID) VALUES (4,3); 
INSERT INTO left_to_right (LID, RID) VALUES (5,1); 
INSERT INTO left_to_right (LID, RID) VALUES (3,5); 
INSERT INTO left_to_right (LID, RID) VALUES (4,2); 

错误的查询。

SELECT 
    DISTINCT left_to_right.LID 
    , COUNT(left_to_right.RID) OVER (PARTITION BY left_to_right.LID) AS NUM_RID 
    , COUNT(left_to_right.RID) OVER (PARTITION BY 
             left_to_right.LID 
            AND 
             right_data.STATUS = 1 
           ) AS NUM_RID_S1 
FROM 
    left_to_right 
    JOIN 
    right_data 
    ON 
    right_data.RID = left_to_right.RID 
ORDER BY left_to_right.LID; 

我怎么能在同一查询LID /（数均right_data.status）和LID数/（right_data.status数为1）？

Answer 1

如果我理解正确的话，这应该这样做：

SELECT left_to_right.LID, 
     COUNT(DISTINCT left_to_right.RID) all_right_data_statuses, 
     COUNT(DISTINCT CASE WHEN right_data.STATUS = 1 THEN left_to_right.RID 
         ELSE NULL END) right_data_status_1 
FROM 
    left_to_right 
    JOIN 
    right_data 
    ON 
    right_data.RID = left_to_right.RID 
GROUP BY left_to_right.LID 

Here is a demo给你试用。

Answer 2

好，最简单的方法不仅会为行，其中right_data.status = 1，那么，试试这个：

COUNT(left_to_right.RID) OVER (PARTITION BY left_to_right.LID) AS NUM_RID 
COUNT(left_to_right.RID) OVER (PARTITION BY left_to_right.LID, right_data.STATUS) as num2, 
(case when right_data.STATUS = 1 
     then COUNT(left_to_right.RID) OVER (PARTITION BY left_to_right.LID, right_data.STATUS)/
      COUNT(left_to_right.RID) OVER (PARTITION BY left_to_right.LID) 
end) 

这是否对你的工作需要什么？