SQL 如何在oracle 12c中的单个选择中计算每个范围分区的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38980793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count records per ranged partition within a single select in oracle 12c?
提问by ScrappyDev
The solution I'm looking for (if it exists) is to directly partition by the actual partition and notby a calculated value.
我正在寻找的解决方案（如果存在）是直接按实际分区而不是按计算值进行分区。
Table Pseudo-Code:
表伪代码：
create table table_1 as (part_col DATE, val_col VARCHAR2(1))
partition by range part_col (
PARTITION t_20160515 VALUES LESS THAN (TO_DATE('2016-05-15','YYYY-MM-DD'))
PARTITION t_20160516 VALUES LESS THAN (TO_DATE('2016-05-16','YYYY-MM-DD'))
...
);
Select Pseudo-Code:
选择伪代码：
select partition as P, count(*) as C from table_1
group by partition;
Desired Result
想要的结果
P | C
-----------|-----
2016-05-15 | 8
2016-05-16 | 99
Works but not what I'm looking for:
有效但不是我正在寻找的：
select trunc(part_col) P, count(*) C from table_1;
回答by xQbert
Run this to generate a set of selects for each partition
运行它为每个分区生成一组选择
SELECT 'SELECT ' || chr(39) || partition_Name || chr(39) || ', count(*)
FROM ' ||table_name ||' partition (' || partition_name ||
') UNION ALL ' as test
FROM all_tab_partitions
WHERE table_Name = 'Table_1'
Results in something like:
结果如下：
SELECT 'P1', count(*) FROM Table_1 partition (P1) UNION ALL
SELECT 'P2', count(*) FROM Table_1 partition (P2) UNION ALL
SELECT 'P3', count(*) FROM Table_1 partition (P3) UNION ALL
SELECT 'P4', count(*) FROM Table_1 partition (P4) UNION ALL
SELECT 'P5', count(*) FROM Table_1 partition (P5) UNION ALL
Copy and paste results removing last union all and then run. Not so sure about performance... I hit a table with 5 partitions and it completed in about 2 sec total rows only around 5 mil.
复制并粘贴结果，删除最后一个联合，然后运行。不太确定性能...我打了一个有 5 个分区的表，它在大约 2 秒内完成，总行数只有大约 500 万。
