Oracle SQL 条件排名 [英] Oracle SQL conditional ranking

问题描述

在我的查询中，我正在执行多种类型的排名，对于其中一种排名类型，我只想在某些列不为空时对行进行排名.否则我不希望排名发生.

In my query, I am doing multiple types of ranking and for one of ranking types, I want to rank the row only if certain column is not null. Else I don't want ranking to happen.

例如这是一个示例表:

+------+------------+------------+--------+--------+
| col1 |    col2    |    col3    | rank 1 | rank 2 |
+------+------------+------------+--------+--------+
| a    | 2018-01-20 | 2018-03-04 |      2 | 2      |
| a    | 2018-01-24 | 2018-04-04 |      1 | 1      |
| b    | 2018-01-02 | 2018-05-03 |      1 | 1      |
| c    | 2017-01-02 | 2017-05-08 |      3 | 2      |
| d    | 2016-05-24 | null       |      1 | null   |
| c    | 2018-02-05 | 2018-05-03 |      2 | 1      |
| c    | 2018-07-28 | null       |      1 | null   |
+------+------------+------------+--------+--------+

rank1 是根据 partition by col1 order by col2 desc 计算的rank 2 的计算方法相同，但仅当 col3 为 null 时，否则应为 null.

rank1 is calculated alright based on partition by col1 order by col2 desc rank 2 should be calculated the same way, but only when when col3 is null, else it should be null.

如何在一个查询中同时获得两个排名?我尝试对 rank2 使用 case 语句，但是当 col3 为空时它会跳过排名，

How can I achieve both ranks in a single query? I tried to use case statement for rank2, but it skips the ranking when col3 is null,

推荐答案

如果我理解正确，您可以尝试使用 CASE WHEN 和 sum 窗口函数

If I understand corrcly, you can try to use CASE WHEN with sum window function

CASE WHEN 检查 col3 不是 null 做累加否则显示 NULL

CASE WHEN check col3 isn't null do accumulate else display NULL

CREATE TABLE T(
  col1 VARCHAR(5),
  col2 DATE,
  col3 DATE
);

INSERT INTO T VALUES ( 'a' , to_date('2018-01-20','YYYY-MM-DD') , to_date('2018-03-04','YYYY-MM-DD'));  
INSERT INTO T VALUES ( 'a' , to_date('2018-01-24','YYYY-MM-DD') , to_date('2018-04-04','YYYY-MM-DD'));  
INSERT INTO T VALUES ( 'b' , to_date('2018-01-02','YYYY-MM-DD') , to_date('2018-05-03','YYYY-MM-DD'));  
INSERT INTO T VALUES ( 'c' , to_date('2017-01-02','YYYY-MM-DD') , to_date('2017-05-08','YYYY-MM-DD'));  
INSERT INTO T VALUES ( 'd' , TO_DATE('2016-05-24','YYYY-MM-DD') , null);  
INSERT INTO T VALUES ( 'c' , TO_DATE('2018-02-05','YYYY-MM-DD') , to_date('2018-05-03','YYYY-MM-DD'));  
INSERT INTO T VALUES ( 'c' , TO_DATE('2018-07-28','YYYY-MM-DD') , null);  

查询 1:

select t1.*,
    rank() OVER(partition by col1 order by col2 desc) rank1,
    (CASE WHEN COL3 IS NOT NULL THEN
       SUM(CASE WHEN COL3 IS NOT NULL THEN 1 ELSE 0 END) OVER(partition by col1 order by col2 desc)
    ELSE
       NULL
    END) rank2
FROM T t1

结果:

| COL1 |                 COL2 |                 COL3 | RANK1 |  RANK2 |
|------|----------------------|----------------------|-------|--------|
|    a | 2018-01-24T00:00:00Z | 2018-04-04T00:00:00Z |     1 |      1 |
|    a | 2018-01-20T00:00:00Z | 2018-03-04T00:00:00Z |     2 |      2 |
|    b | 2018-01-02T00:00:00Z | 2018-05-03T00:00:00Z |     1 |      1 |
|    c | 2018-07-28T00:00:00Z |               (null) |     1 | (null) |
|    c | 2018-02-05T00:00:00Z | 2018-05-03T00:00:00Z |     2 |      1 |
|    c | 2017-01-02T00:00:00Z | 2017-05-08T00:00:00Z |     3 |      2 |
|    d | 2016-05-24T00:00:00Z |               (null) |     1 | (null) |

这篇关于Oracle SQL 条件排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！