Oracle SQL按列进行计数，但仅当该列为null或0时

小编典典

Oracle SQL按列进行计数，但仅当该列为null或0时

sql

简而言之，我尝试按列分组，但前提是该列未分组，null或者0在这种情况下，我仍然希望获得这些行，但不进行分组。我有这张桌子some_table：

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | abc      | 2011-04-20 21:03:05 | 104   |
| 2      | abc      | 2011-04-20 21:03:04 | 229   |
| 3      | xyz      | 2011-04-20 21:03:03 | 130   |
| 4      | abc      | 2011-04-20 21:02:09 | 97    |
| 5      | 0        | 2011-04-20 21:02:08 | 65    |
| 6      | xyz      | 2011-04-20 21:02:07 | 101   |
| 7      |          | 2011-04-20 21:02:07 | 200   |
| 8      | 0        | 2011-04-20 21:02:07 | 201   |
| 9      |          | 2011-04-20 21:02:07 | 202   |
+--------+----------+---------------------+-------+

我想选择行和分组依据other_id，还包括对分组行的计数。该查询有效：

select id, other_id, date_value, value, cnt from
 (
   SELECT id, other_id, date_value, value, 
   ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r,
   count(*) OVER (partition by other_id) cnt
   FROM some_table 
 )
where r = 1

结果是：

+--------+----------+---------------------+-------+-------+
| id     | other_id | date_value          | value | count |
+--------+----------+---------------------+-------+-------+
| 1      | abc      | 2011-04-20 21:03:05 | 104   | 3     |
| 3      | xyz      | 2011-04-20 21:03:03 | 130   | 2     |
| 5      | 0        | 2011-04-20 21:02:08 | 65    | 2     |
| 7      |          | 2011-04-20 21:02:07 | 200   | 2     |
+--------+----------+---------------------+-------+-------+

问题是，我不希望组具有行other_id的null或0。因此，理想的结果是这样的：

+--------+----------+---------------------+-------+-------+
| id     | other_id | date_value          | value | count |
+--------+----------+---------------------+-------+-------+
| 1      | abc      | 2011-04-20 21:03:05 | 104   | 3     |
| 3      | xyz      | 2011-04-20 21:03:03 | 130   | 2     |
| 5      | 0        | 2011-04-20 21:02:08 | 65    | 1     |
| 7      |          | 2011-04-20 21:02:07 | 200   | 1     |
| 8      | 0        | 2011-04-20 21:02:07 | 201   | 1     |
| 9      |          | 2011-04-20 21:02:07 | 202   | 1     |
+--------+----------+---------------------+-------+-------+

正如你所看到的，一切都只是当分组other_id是null或0在这种情况下，他们仍然选择，但不进行分组。我也想按订单订购date_value。

我尝试在WHERE子句中添加一个OVER子句：

OVER (partition by other_id WHERE other_id IS NOT NULL AND other_id IS NOT '0' order BY Date_Value desc)
-- this produces an error: ORA-00907: missing right parenthesis

我曾考虑过进行第二次select查询，并尝试将第二个结果堆叠在第二个查询之上，但是由于排序的原因，我认为这行不通。

有没有办法像这样将一个条件分组？

阅读 152

2021-04-22

共1个答案

小编典典

可以做到这一点：

SQL> select id, other_id, date_value, value, cnt from
  2   (
  3     SELECT id, other_id, date_value, value,
  4     case
  5       when other_id is null or other_id = '0' then 1
  6       else ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc)
  7     end r,
  8     case
  9       when other_id is null or other_id = '0' then 1
 10       else count(*) OVER (partition by other_id)
 11     end cnt
 12     FROM some_table
 13   )
 14  where r = 1
 15  order by id;

        ID OTH DATE_VALUE               VALUE        CNT
---------- --- ------------------- ---------- ----------
         1 abc 2011-04-20 21:03:05        104          3
         3 xyz 2011-04-20 21:03:03        130          2
         5 0   2011-04-20 21:02:08         65          1
         7     2011-04-20 21:02:07        200          1
         8 0   2011-04-20 21:02:07        201          1
         9     2011-04-20 21:02:07        202          1

6 rows selected.

SQL>

2021-04-22