需要SQL查询/脚本为每个列提供一个表中每列的独特计数

I need a query/script to just display a distinct count of values in each column of a table. I'm using it to tie out to a legacy report where each column is a two way freq of the column by disctinct count. Something like below:

我需要一个查询/脚本来在表的每一列中显示不同的值计数。我正在使用它来绑定遗留报告,其中每列是按行分别计算的列的双向频率。如下所示:

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'

3 个解决方案

#1

Please find below script for generating SQL query:

请在下面找到生成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;

You will get some query like this:

你会得到一些像这样的查询:

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

And after run this query result will be like this:

运行后,此查询结果将如下所示:

col     cnt
field2  5
field1  3
field3  1

#2

This would give the results for each column in a single row

这将给出单行中每列的结果

select Field1Count = count(distinct field1)
,Field2Count = count(distinct field2)
,fieldetcCount = count(fieldetc)
from EBL_CLIENT.EAP_FACT
where run_id = '205572'

#3

I don't know if this helps as it will still run 117 queries but you won't have to manually create them. Run the query which will return 117 select statements. Copy them and run them to get the counts.

我不知道这是否有帮助,因为它仍然会运行117个查询,但您不必手动创建它们。运行将返回117个select语句的查询。复制它们并运行它们以获得计数。

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'

#1