Oracle SQL按列计数，但只有在列为空或0时使用 [英] Oracle SQL group by column with count but only if the column is null or 0
问题描述
简而言之，我试图按列进行分组，但只有当列不是 null 或 0 在这种情况下，我仍然想获得这些行，但只是没有分组。我有这张表 some_table ：
+ ------ - + ---------- + --------------------- + ------- +
| id | other_id | date_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选择行和组，并且还包括分组行的计数。这个查询的工作原理：
$ b $ pre $ code $ select $ id
$ ，other_id，date_value，value，
ROW_NUMBER（）OVER（由other_id命令分区BY BY Date_Value desc）r，$ b $ count（*）OVER（由other_id分区）cnt
FROM some_table
）
其中r = 1
结果是：
+ -------- + ---------- + ------------ --------- + ------- + ------- +
| id | other_id | date_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 |值| 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（由other_id分区WHERE other_id不为'0'order BY Date_Value desc）
- 产生一个错误：ORA-00907：缺少右括号
我想过要做第二个选择查询并试图将第一个结果堆叠在顶部第二，但我不认为这将工作，因为订购。
有没有任何方式像这样的条件？
这样做：
SQL> ; select_id，other_id，date_value，value，cnt from
2（
3 SELECT id，other_id，date_value，value，
4 case
other_id为null或other_id =' 0'然后1
6 else ROW_NUMBER（）OVER（由other_id命令分区BY Date_Value desc）
7 end r，
8 case
当other_id为null或other_id =' 0'then 1
10 else count（*）OVER（by other_id分区）
11 end cnt
12 FROM some_table
13）
14其中r = 1
15按ID排序;
ID OTH DATE_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行。
SQL>
In a nutshell I'm trying to group by a column but only if that column is not null or 0 in which case I still want to get those rows, but just not grouped. I have this table 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 |
+--------+----------+---------------------+-------+
I wanted to select the rows and group by other_id, and also include a count of the grouped rows. This query works:
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
The result is:
+--------+----------+---------------------+-------+-------+
| 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 |
+--------+----------+---------------------+-------+-------+
The problem is, I don't want to group the rows that have other_id of null or 0. So the desired result is this:
+--------+----------+---------------------+-------+-------+
| 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 |
+--------+----------+---------------------+-------+-------+
As you can see, everything is grouped except when other_id is null or 0 in which case they are still selected, but not grouped. I am also trying to order by the date_value.
I have tried adding a WHERE clause in the OVER clause:
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
I have thought about doing a second select query and trying to stack the first results on top of the second, but I don't think that would work because of the ordering.
Is there any way of grouping like this with a condition?
this will do it:
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>
这篇关于Oracle SQL按列计数，但只有在列为空或0时使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
