分别计算每列的不同值 [英] Count distinct values for every column individually

问题描述

我可以在不枚举它们的情况下计算每列的不同值吗?

Can I count distinct values of every column without enumerating them ?

说我有一个表，其中包含 col1 ， col2 ， col3 ，没有其他列.在没有明确提及这些列的情况下，我希望得到与以下内容相同的结果:

Say I have a table with col1, col2, col3, and no other column. Without mentioning these columns explicitly, I would like to have the same result as:

SELECT
count(distinct col1) as col1,
count(distinct col2) as col2,
count(distinct col3) as col3
FROM mytable;

我该怎么做?

推荐答案

我认为，使用普通SQL可以轻松做到的最好是运行这样的查询以生成所需的查询，然后运行该查询.

I think the best you could easily do with plain SQL is to run a query like this to generate the query you want, and then run that.

select 'select count(distinct '
    || listagg(column_name || ') as ' || column_name, ', count(distinct ') within group (order by column_id) 
    || ' from ' || max(table_name) || ';' as script
from all_tab_cols
where table_name = 'MYTABLE';

这篇关于分别计算每列的不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！