首页 文章

从表格中的每一列中获取前三个最常见的值

提问于
浏览
3

我正在尝试编写一个查询,该查询将从表的每列产生非常小的数据样本,其中样本由前3个最常见的值组成 . 这个特殊问题是一项更大任务的一部分,即编写可以表征数据库及其表的脚本,数据完整性,并且还可以按列逐步调查表中的常见值 . 可以将其视为表格的自动“分析” .

在单列的基础上,我已经通过简单地计算值的频率然后按频率排序来做到这一点 . 如果我有一个名为“颜色”的列并且所有颜色都在其中,并且恰好在大多数行中颜色“蓝色”,那么最常出现的前1个值将是“蓝色” . 在SQL中很容易计算 .

但是,我不确定如何在多列上执行此操作 .

目前,当我对表的所有列进行计算时,我执行以下类型的查询:

USE database;

DECLARE @t nvarchar(max)
SET @t = N'SELECT '

SELECT @t = @t + 'count(DISTINCT CAST(' + c.name + ' as varchar(max))) "' + c.name + '",'
FROM sys.columns c 
WHERE c.object_id = object_id('table');

SET @t = SUBSTRING(@t, 1, LEN(@t) - 1) + ' FROM table;'

EXEC sp_executesql @t

但是,我并不完全清楚我将如何做到这一点 .

(旁注:类型为text,ntext和image的列,因为这些会在计算不同的值时导致错误,但我不太关心解决这个问题)

但是每列最常见三个值的问题让我感到非常难过 .

理想情况下,我想最终得到这样的东西:

Col1     Col2              Col3       Col4     Col5
---------------------------------------------------------------------
1,2,3    red,blue,green    29,17,0    c,d,j    nevada,california,utah

2 回答

  • 0

    我一起攻击这个,但它似乎工作:

    我不能帮助,但认为我应该使用RANK() .

    USE <DB>;
    
    DECLARE @query nvarchar(max)
    DECLARE @column nvarchar(max)
    DECLARE @table nvarchar(max)
    DECLARE @i INT = 1
    DECLARE @maxi INT = 10
    DECLARE @target NVARCHAR(MAX) = <table>
    
    declare @stage TABLE (i int IDENTITY(1,1), col nvarchar(max), tbl nvarchar(max))
    declare @results table (ColumnName nvarchar(max), ColumnValue nvarchar(max), ColumnCount int, TableName NVARCHAR(MAX))
    
    insert into @stage
    
    select c.name, o.name
        from sys.columns c
        join sys.objects o on o.object_id=c.object_id and o.type = 'u'
        and c.system_type_id IN (select system_type_id from sys.types where [name] not in ('text','ntext','image'))
        and o.name like @target
    
    
    
    SET @maxi = (select max(i) from @stage)
    
    while @i <= @maxi
    
    BEGIN
    
    set @column = (select col from @stage where i = @i)
    set @table = (select tbl from @stage where i = @i)
    
    
    SET @query = N'SELECT ' +''''+@column+''''+' , '+ @column
    
    SELECT @query = @query + ', COUNT(  ' + @column + ' ) as count' + @column + ' , ''' + @table + ''' as tablename'
    select @query = @query + ' from ' + @table + ' group by ' + @column
    
    --Select @query
    insert into @results
    EXEC sp_executesql @query
    
    SET @i = @i + 1
    END
    
    select * from @results
    ; with cte as (
                    select *, ROW_NUMBER() over (partition by Columnname order by ColumnCount desc) as rn from @results
                    )
    
    
    select * from cte where rn <=3
    
  • 1

    从这个SQL语句构建器开始,并根据自己的喜好进行修改:

    EDIT 添加了按说明排序

    With ColumnSet As
    (
        Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
        From INFORMATION_SCHEMA.COLUMNS
        Where 1=1
            And TABLE_NAME IN ('Table1')
            And COLUMN_NAME IN ('Column1', 'Column2')
    )
    Select 'Select Top 3 ' + COLUMN_NAME + ', Count (*) NumInstances From ' + TABLE_SCHEMA + '.'+ TABLE_NAME + ' Group By ' + COLUMN_NAME + ' Order by Count (*) Desc'
    From ColumnSet
    

相关问题