需要SQL查询/脚本给我一个表中每列的不同计数
我需要一个查询/脚本来显示表中每列的不同数量的值。我使用它来与传统报告结合起来,其中每列是由列数的双向频率。类似下面:需要SQL查询/脚本给我一个表中每列的不同计数
select distinct field1,count(*)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'
select distinct field2,count(*)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'
select distinct fieldetc...,count(*)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'
这将使每个列的结果在单行
select Field1Count = count(distinct field1)
,Field2Count = count(distinct field2)
,fieldetcCount = count(fieldetc)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'
'Field1Count = count(distinct field1)'无效SQL(在SQL标准和Oracle中) –
我不知道如果这能帮助,因为它仍然会运行117个查询,但你不会必须手动创建它们。运行将返回117个选择语句的查询。复制它们并运行它们以获取计数。
SELECT 'SELECT ''' || COLUMN_NAME || ''' AS ColumnName , COUNT(DISTINCT '
|| COLUMN_NAME || ') AS Count FROM ' || Table_Schema || '.' || Table_Name
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_SCHEMA = 'EBL_CLIENT'
AND TABLE_NAME = 'EAP_FACT'
SQL中的字符串连接运算符是'||'not'+' –
@a_horse_with_no_name对不起,在想SQL服务器 – SQLChao
请在下面找到脚本生成的SQL查询:
declare
v_col varchar2(64) := 'run_id';
v_val varchar2(64) := '205572';
v_table varchar2(64) := 'EAP_FACT';
v_schema varchar2(64) := 'EBL_CLIENT';
begin
dbms_output.put_line('select *'||chr(10)||'from (select ');
for i in (select t.COLUMN_NAME, rownum rn
from all_tab_columns t
where t.TABLE_NAME = upper(v_table)
and t.OWNER = upper(v_schema)
and t.COLUMN_NAME <> upper(v_col)
order by t.COLUMN_ID)
loop
dbms_output.put_line(' '||case when i.rn=1 then ' ' else ',' end||
'count(distinct '||i.column_name||') '||i.column_name);
end loop;
dbms_output.put_line(' from '||v_schema||'.'||v_table||' t where t.'||v_col||' = '''||v_val||''')'
||chr(10)||'unpivot'||chr(10)||'(cnt');
for i in (select listagg (t.COLUMN_NAME,',') within group (order by t.COLUMN_ID) lst
from all_tab_columns t
where t.TABLE_NAME = upper(v_table)
and t.OWNER = upper(v_schema)
and t.COLUMN_NAME <> upper(v_col))
loop
dbms_output.put_line(' '||'for col in ('||i.lst||')');
end loop;
dbms_output.put_line(')'||chr(10)||'order by cnt desc');
end;
你会得到一些这样的查询:
select *
from (select
count(distinct t.field1) field1
,count(distinct t.field2) field2
,count(distinct t.field3) field3
from EBL_CLIENT.EAP_FACT t where t.run_id = '205572')
unpivot
(cnt
for col in (field1,field2,field3)
)
order by cnt desc
并运行后,此查询的结果会是这样:
col cnt
field2 5
field1 3
field3 1
这通常是用'SE LECT列,COUNT(*)FROM表GROUP BY列。 – Kenney
可能很难获得** all **列的值的计数,因为如果列的类型为CLOB,LOB,BLOB,LONG,XML,Object,嵌套表格等,则无法获得明确的计数。 – krokodilko
是的,我只是想尽量减少我必须运行的查询。我在SQL方面经验不足,不知道是否有可运行的脚本,可以为EAP_FACT表提供每个字段的不同值计数。有117个不同的领域。 – dag06001