时间戳

首页 > 解决方案 > 检查列是否有空值

sql - 检查列是否有空值

问题描述

请告诉我如何编写以下算法。按“不为空”的相同“clusterid”过滤。

  1. 如果“issuedate”是一个空列，则取“publid”，它有更多的“inn”。
  2. 如果 "issuedate" 不完全相等，那么我们取 "issuedate" = last date。
  3. 如果 "issuedate" 都相等，那么我们取 "operdate" = last date。
  4. 如果 "issuedate" 和 operdate 相等，那么我们取 "publid"，它有更多的 "inn"。

这就是我得到的，这里满足条件2,3,4。但我不知道如何在代码中插入 1 个条件。

SELECT m.* 
FROM (SELECT a.*, ROW_NUMBER() over (partition by clusterid, inn order by cnt desc) rn
   FROM (SELECT b.* ,COUNT(inn) OVER (PARTITION BY publid) cnt 
   FROM (SELECT c.*, RANK() OVER (PARTITION BY clusterid order by issuedate desc,operdate desc) rnk
       FROM table as c
       WHERE clusterid is not null) as b
WHERE b.rnk=1) as a
 ) as m 
WHERE m.rn=1

我想也许通过“case when”，但我不知道如何正确地做到这一点。如果您能提供帮助，我将不胜感激。

SELECT m.* 
FROM (SELECT a.*, ROW_NUMBER() over (partition by clusterid, inn order by cnt desc) rn
   FROM (SELECT b.* ,COUNT(inn) OVER (PARTITION BY publid) cnt 
       FROM (SELECT c.*, CASE WHEN issuedate='' then OVER (PARTITION BY clusterid)
       else RANK() OVER (PARTITION BY clusterid order by issuedate desc,operdate desc) 
       end rnk
           FROM table as c
           WHERE clusterid is not null) as b
WHERE b.rnk=1) as a
 ) as m 
WHERE m.rn=1

例子

|*inn*|*publid*|*clusterid*|*issuedate*|*operdate*|
|-----|--------|-----------|-----------|----------|
| 333 |   1    |    12     |  01-01-21 | 05-01-21 |
| 222 |   1    |    12     |  01-01-21 | 05-01-21 |
| 333 |   2    |    12     |  01-01-21 | 05-01-21 | -condition 3
| 222 |   2    |    12     |  01-01-21 | 05-01-21 |
| 111 |   2    |    12     |  01-01-21 | 05-01-21 |
|-----|--------|-----------|-----------|----------|
| 123 |   1    |     1     |  01-01-21 |          |
| 456 |   1    |     1     |  01-01-21 |          |
| 123 |   2    |     1     |  03-01-21 |          |
| 456 |   2    |     1     |  03-01-21 |          | - condition 4
| 789 |   2    |     1     |  03-01-21 |          |
| 123 |   3    |     1     |  02-01-21 |          |
| 456 |   3    |     1     |  02-01-21 |          |
|-----|--------|-----------|-----------|----------|
| 123 |   1    |     1     |           | 01-01-21 |
| 456 |   1    |     1     |           | 01-01-21 |
| 123 |   2    |     1     |           | 03-01-21 |
| 456 |   2    |     1     |           | 03-01-21 | - condition 1
| 789 |   2    |     1     |           | 03-01-21 |
| 123 |   3    |     1     |           | 02-01-21 |
| 456 |   3    |     1     |           | 02-01-21 |

结果

|*inn*|*publid*|*clusterid*|*issuedate*|*operdate*|
|-----|--------|-----------|-----------|----------|
| 333 |   2    |    12     |  01-01-21 | 05-01-21 |
| 222 |   2    |    12     |  01-01-21 | 05-01-21 |- condition 2
| 111 |   2    |    12     |  01-01-21 | 05-01-21 |
|-----|--------|-----------|-----------|----------|
| 123 |   2    |     1     |  03-01-21 |          |
| 456 |   2    |     1     |  03-01-21 |          |- condition 3
| 789 |   2    |     1     |  03-01-21 |          |
|-----|--------|-----------|-----------|----------|
| 123 |   2    |     1     |           | 03-01-21 |
| 456 |   2    |     1     |           | 03-01-21 |- condition 1
| 789 |   2    |     1     |           | 03-01-21 |

标签： sqloracle

解决方案

看来您想要类似的东西：

SELECT inn,
       publid,
       clusterid,
       issuedate,
       operdate
FROM   (
  SELECT inn,
         publid,
         clusterid,
         issuedate,
         operdate,
         DENSE_RANK() OVER (
           PARTITION BY clusterid
           ORDER     BY issuedate DESC NULLS LAST,
                        CASE WHEN issuedate IS NULL THEN inn END DESC,
                        operdate DESC NULLS LAST,
                        cnt DESC
         ) AS rnk
  FROM   (
    SELECT t.*,
           COUNT(inn) OVER (PARTITION BY publid) cnt
    FROM   table_name t
    WHERE  clusterid is not null
  )
)
WHERE  rnk = 1;

其中，对于您的示例数据：

CREATE TABLE table_name (inn, publid, clusterid, issuedate, operdate ) AS
SELECT 333, 1, 12, DATE '2021-01-01', DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 222, 1, 12, DATE '2021-01-01', DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 333, 2, 12, DATE '2021-01-01', DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 222, 2, 12, DATE '2021-01-01', DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 111, 2, 12, DATE '2021-01-01', DATE '2021-01-05' FROM DUAL UNION ALL
SELECT 123, 1,  1, DATE '2021-01-01', NULL FROM DUAL UNION ALL
SELECT 456, 1,  1, DATE '2021-01-01', NULL FROM DUAL UNION ALL
SELECT 123, 2,  1, DATE '2021-01-03', NULL FROM DUAL UNION ALL
SELECT 456, 2,  1, DATE '2021-01-03', NULL FROM DUAL UNION ALL
SELECT 789, 2,  1, DATE '2021-01-03', NULL FROM DUAL UNION ALL
SELECT 123, 3,  1, DATE '2021-01-02', NULL FROM DUAL UNION ALL
SELECT 456, 3,  1, DATE '2021-01-02', NULL FROM DUAL;

输出：

客栈 公开 集群 发行日期 操作日期
123 2 1 21 年 1 月 3 日
789 2 1 21 年 1 月 3 日
456 2 1 21 年 1 月 3 日
111 2 12 01-JAN-21 21 年 1 月 5 日
333 2 12 01-JAN-21 21 年 1 月 5 日
222 2 12 01-JAN-21 21 年 1 月 5 日

db<>在这里摆弄

更新

SELECT inn,
       publid,
       clusterid,
       issuedate,
       operdate
FROM   (
  SELECT inn,
         publid,
         clusterid,
         issuedate,
         operdate,
         DENSE_RANK() OVER (
           PARTITION BY clusterid
           ORDER     BY COALESCE( issuedate, operdate ) DESC NULLS LAST,
                        cnt DESC
         ) AS rnk
  FROM   (
    SELECT t.*,
           COUNT(inn) OVER (PARTITION BY publid) cnt
    FROM   table_name t
    WHERE  clusterid is not null
  )
)
WHERE  rnk = 1;

db<>在这里摆弄

推荐阅读