Oracle SQL-如何动态获取每列的不重复计数? [英] Oracle SQL - How to get distinct count for each column dynamically?

问题描述

我发现的类似问题似乎并没有专门回答如何在不键入查询的每一列的情况下，对ALL列的不同元素进行单独计数的问题.

The similar questions I've found don't seem to specifically answer how to get the separate counts on distinct elements of ALL columns without typing out each column out in a query.

我想对所有列执行类似的操作，但不要在每一列中都输入:

I want to do something like this, for all columns, but without typing each column out:

SELECT     
  COUNT(distinct COL1) AS COL1DISTINCT,     
  COUNT(distinct COL2) AS COL2DISTINCT    
FROM TABLE_NAME;

但是我们不想键入所有单独的列，因为需要在几个不同客户端上的其他几个表上检索相同的数据，所有这些表每次都具有不同的列名.

But we don't want to type out all the individual columns because the same data needs to be retrieved on several other tables across several different clients, all of which will have different column names each time.

我试图找到一种访问列名以创建子查询的方法，但是我们的元数据表为空，不提供任何列名.

I tried finding a way to access the column names to create subqueries, but our meta-data tables are empty and don't provide any column names.

推荐答案

如果您同意每列一个结果集行，则可以采用

If you're OK with one result-set row per column you could adapt this XML magic trick:

select owner, table_name, column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(distinct "' || column_name || '") as c '
      || 'from "' || owner || '"."' || table_name || '"'))
  returning content)) as c
from all_tab_columns
where owner = '<your table owner>'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2');

...列出了您需要能够计数的所有数据类型；确实是要排除不能处理distinct的对象(如CLOB)，但是由于您可能有嵌套表等，因此列出要执行的对象可能会更简单/em>希望并希望能够计数.

... listing all the data types you need to be able to count; really that is to exclude those that can't handle distinct like CLOB, but as you may have nested tables etc. as well it's probably going to be simpler to list those you do want and expect to be able to count.

dbms_xmlgen()调用将有效地动态构造的该select count(distinct ...) ...查询的结果转换为XML结构，然后您可以使用XMLQuery()从该计数中取出计数(而不是已弃用的在链接的答案中.

The dbms_xmlgen() call converts the result of that select count(distinct ...) ... query, which is effectively constructed dynamically, into an XML structure, and you can then pull the count out from that with XMLQuery() (instead of the deprecated extractvalue() in the linked answer).

作为一个非常快速的演示:

As a very quick demo:

create table t42 (id number, str varchar2(20));
insert into t42 values (1, 'Test');
insert into t42 values (2, 'Test');
insert into t42 values (3, 'Test 2');
insert into t42 values (3, null);

select owner, table_name, column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(distinct "' || column_name || '") as c '
      || 'from "' || owner || '"."' || table_name || '"'))
  returning content)) as c
from all_tab_columns
where owner = 'MY_SCHEMA'
and table_name = 'T42'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2');

OWNER           TABLE_NAME      COLUMN_NAME              C
--------------- --------------- --------------- ----------
MY_SCHEMA       T42             ID                       3
MY_SCHEMA       T42             STR                      2

是否还有一种方法还可以在每列中获取空计数?

Is there a way to also get a count of nulls in every column as well?

count()函数将忽略空值，因此要计算这些值，您必须将其转换，例如与

The count() function ignores nulls, so to count those you have to convert them, e.g. with

count(case when <your_column> is null then 1 end)

您可以在其中添加第二个XMLQuery子句:

You an include that here either with a second XMLQuery clause:

select owner, table_name, column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(distinct "' || column_name || '") as c '
      || 'from "' || owner || '"."' || table_name || '"'))
  returning content)) as distinct_count,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(case when "' || column_name || '" is null then 1 end) as c '
      || 'from "' || owner || '"."' || table_name || '"'))
  returning content)) as null_count
from all_tab_columns
where owner = 'MY_SCHEMA'
and table_name = 'T42'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
    'NCHAR', 'NVARCHAR2');

OWNER           TABLE_NAME      COLUMN_NAME     DISTINCT_COUNT NULL_COUNT
--------------- --------------- --------------- -------------- ----------
MY_SCHEMA       T42             ID                           3          0
MY_SCHEMA       T42             STR                          2          1

或使用一个XMLTable从生成的XML中提取两个列值，并对其进行了修改以同时进行两个计数:

or with a single XMLTable that extracts both column values from the generated XML, which is modified to do both counts at once:

select a.owner, a.table_name, a.column_name,
  x.distinct_count, x.null_count
from
(
  select owner, table_name, column_name,
    dbms_xmlgen.getxml(
      'select count(distinct "' || column_name || '") as c1,'
        || 'count(case when "' || column_name || '" is null then 1 end) as c2 '
      || 'from "' || owner || '"."' || table_name || '"') as xml_clob
  from all_tab_columns
  where owner = 'MY_SCHEMA'
  and table_name = 'T42'
  and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
    'NCHAR', 'NVARCHAR2')
) a
cross join xmltable (
  '/ROWSET/ROW'
  passing xmltype(a.xml_clob)
  columns distinct_count number path 'C1',
    null_count number path 'C2'
) x;

OWNER           TABLE_NAME      COLUMN_NAME     DISTINCT_COUNT NULL_COUNT
--------------- --------------- --------------- -------------- ----------
MY_SCHEMA       T42             ID                           3          0
MY_SCHEMA       T42             STR                          2          1

这篇关于Oracle SQL-如何动态获取每列的不重复计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！