Блог

У меня есть таблица с записями, в которой 100 столбцов, мне нужно получить количество различных значений всех столбцов из этой таблицы на основе некоторого условия (предложение where).

Приведенный ниже запрос работает нормально, но я не могу использовать предложение where. Таким образом, он выдает результат для всех записей таблицы. Но я хочу, чтобы это было основано на некотором условии, скажем, столбец file_id = 1; . Мой вопрос заключается в том, как использовать предложение where с приведенным ниже запросом. Или, если есть какой-либо другой альтернативный способ решения этой проблемы.

 declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select Table_Schema, Table_Name, Column_Name, Row_Number() over(partition by Table_Schema, Table_Name
order by ORDINAL_POSITION) as RowNum
from INFORMATION_SCHEMA.COLUMNS
)

select @SQL = @SQL   case when RowNum = 1 then '' else ' union all ' end
  ' select '''   Column_Name   ''' as Column_Name, count(distinct '   quotename (Column_Name)   ' ) As DistinctCountValue, 
count( '  quotename (Column_Name)   ') as CountValue FROM '   quotename (Table_Schema)   '.'   quotename (Table_Name)
from cols
where Table_Name = 'table_name' --print @SQL

execute (@SQL)
  

Я использую динамический запрос, потому что мне нужно повторно использовать этот запрос и для других таблиц.

Сначала получите столбцы и используйте stuff для генерации select таким образом:

 SELECT COUNT(ColumnA) AS ColumnA, COUNT(ColumnB AS ColumnB), COUNT(ColumnC) AS ColumnC....
  

Таким образом, вы выбираете в своей таблице только один раз, чтобы получить все подсчеты, после этого используйте CROSS APPLY для «открепления» этих столбцов и возврата выходных данных по одной строке на столбец

 CROSS APPLY(
    VALUES(1, 'ColumnA', ColumnA), (2, 'ColumnB', ColumnB), (3, 'ColumnC', ColumnC)
)(ID, ColumnName, DistinctCountValue)
  

Для фильтра используйте sp_executesql и отправьте file_id в качестве параметра

 exec SP_executesql @SQL, N'@FID INT', @FID = @FileID
  

Поскольку вы используете все столбцы таблицы, Row_Number() over(partition by Table_Schema, Table_Name order by ORDINAL_POSITION) as RowNum становится избыточным, ORDINAL_POSITION уже имеет значение, которое вы ищете

 declare @tablename nvarchar(50) = 'MyTestTable'
declare @fileID int = 1
declare @SQL nvarchar(max)
set @SQL = ''
;with cols as (
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName
)

select @SQL = ';WITH  CTE AS (SELECT 
'   
    STUFF((
    SELECT ', COUNT(DISTINCT '   QUOTENAME(COLUMN_NAME)   ') AS '   QUOTENAME(COLUMN_NAME)
    FROM cols
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')
    ), 1, 1, '')
  '
FROM '   @TableName   '
WHERE File_ID = @FID
)
SELECT B.*
FROM CTE
CROSS APPLY (
    VALUES '  STUFF((
    SELECT ',( '   CAST(ORDINAL_POSITION AS VARCHAR)   ','   QUOTENAME(COLUMN_NAME,'''')   ','   QUOTENAME(COLUMN_NAME)   ')'
    FROM cols
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')
    ), 1, 1, '')   '
)B (ID,ColumnName,DistinctCountValue) 
'
from cols


exec SP_executesql @SQL, N'@FID INT', @FID = @FileID
  

Приведенный ниже запрос создает таблицу всех имен столбцов и использует цикл while для выбора count для любого предложения WHERE, которое вы хотите использовать. Это должно быть довольно гибким для любой таблицы; просто обновите верхние переменные. Обратите внимание, что при этом не будет учитываться столбец, значение которого равно null. Вы можете добавить регистр к параметру @Query, если это то, что вы хотите. Поскольку он обрабатывает каждую строку по отдельности, я добавил временную таблицу, чтобы вы обращались к базе данных только один раз.

 IF OBJECT_ID('tempdb..##SourceValues') IS NOT NULL
    DROP TABLE ##SourceValues
DECLARE @Schema VARCHAR(50) = 'SomeSchema'
DECLARE @Table VARCHAR(50) = 'SomeTable'
DECLARE @WhereClause VARCHAR(MAX) = ' Some WHERE clause'
DECLARE @ColumnName VARCHAR(50)
DECLARE @ProcessedRows TABLE(ColumnName VARCHAR(50), DistinctCount INT)
DECLARE @Columns TABLE(RowNumber INT,  ColumnName VARCHAR(100)) 
INSERT INTO @Columns SELECT ROW_NUMBER() OVER(ORDER BY COLUMN_NAME DESC),  COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table
DECLARE @Count INT = (SELECT MAX(RowNumber) FROM @Columns)
DECLARE @Counter INT = 0
DECLARE @DistinctCount INT 
DECLARE @Query NVARCHAR(MAX)
EXEC('SELECT * INTO ##SourceValues FROM '   @Table  ' (NOLOCK)')
WHILE @Counter < @Count
BEGIN 
    SET @Counter  = 1
    SET @ColumnName = (SELECT ColumnName FROM @Columns WHERE RowNumber = @Counter)
    SET @Query = 'SELECT @OutPut = COUNT('   @ColumnName   ') FROM '   @Schema   '.'   ' ##SourceValues '   @WhereClause
    EXECUTE sp_executesql @Query, N'@Output INT OUT', @DistinctCount OUT
    INSERT INTO @ProcessedRows(ColumnName, DistinctCount) VALUES (@ColumnName, @DistinctCount)
END
SELECT * FROM @ProcessedRows
  

Давайте попробуем другой подход.

Получить все значения, не представленные как Param / Value:

1) Соберите список таблиц и столбцов, которые будут использоваться в динамическом SQL:

 DROP TABLE IF EXISTS #Base;
;WITH SchemaData AS (
    SELECT t.name AS [TableName],c.name AS [ColumnName],c.column_id AS [ColumnOrderID]
    FROM sys.tables t
    INNER JOIN sys.columns c ON c.object_id = t.object_id
)
SELECT t.TableName
    ,STUFF((SELECT ',CONVERT(NVARCHAR(MAX),'   QUOTENAME([ColumnName])   ') AS '   QUOTENAME([ColumnName]) 
            FROM SchemaData a WHERE (a.TableName = t.TableName) FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'') AS [SelectClause]
    ,STUFF((SELECT ','   QUOTENAME([ColumnName]) FROM SchemaData a WHERE (a.TableName = t.TableName) FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'') AS [UnpivotClause]
INTO #Base
FROM SchemaData t
GROUP BY t.TableName
;
  

2) Получить все данные внутри временной таблицы

 DROP TABLE IF EXISTS #Result;
CREATE TABLE #Result(TableName NVARCHAR(255),ColumnName NVARCHAR(255),[Value] NVARCHAR(MAX));

DECLARE @TableName NVARCHAR(255),@SelectClause NVARCHAR(MAX),@UnpivotClause NVARCHAR(MAX);
DECLARE crPopulateResult CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT b.TableName,b.SelectClause,b.UnpivotClause FROM #Base b;

OPEN crPopulateResu<
FETCH NEXT FROM crPopulateResult INTO @TableName,@SelectClause,@UnpivotClause;

DECLARE @dSql NVARCHAR(MAX);
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @dSql = N'   INSERT INTO #Result(TableName,[ColumnName],[Value])
    SELECT up.TableName,up.Param AS [ColumnName],up.[Value]
    FROM (
        SELECT '''   @TableName   N''' AS [TableName]
            ,'   @SelectClause   N'
        FROM '   QUOTENAME(@TableName)   N'
    ) a
    UNPIVOT(Value FOR Param IN ('   @UnpivotClause   N')) up
    ';
    EXEC sp_executesql @stmt = @dSql;

    FETCH NEXT FROM crPopulateResult INTO @TableName,@SelectClause,@UnpivotClause;
END

CLOSE crPopulateResu<
DEALLOCATE crPopulateResu<
  

3) С #Results могут быть применены любые фильтры, включая имена таблиц, имена столбцов, фильтры данных и т. Д:

 SELECT r.TableName,r.ColumnName,COUNT(*) AS [CountValue],COUNT(DISTINCT r.[Value]) AS [DistinctCountValue]
FROM #Result r
--
--WHERE r.ColumnName = 'file_id' AND r.[Value] = '1'
--
GROUP BY r.TableName,r.ColumnName
ORDER BY r.TableName,r.ColumnName
;
  

Чтобы использовать это с предложением where в этом запросе, вам просто нужно поместить предложение where в конструкцию после имени таблицы, поэтому, если вы хотите выполнить фильтрацию по file_id =’1′, тогда у вас будет:

 FROM '   quotename (Table_Schema)   '.'   quotename (Table_Name)  'where file_id =''1'' '
  

Вы можете добавить @where переменную и объединить ее с вашей конструкцией big union (как часть вашей select ... from cols ). Например:

 declare @SQL nvarchar(max)
declare @where nvarchar(max) = ' where file_id = 1'

set @SQL = ''
;with cols as (
select Table_Schema, Table_Name, Column_Name, Row_Number() over(partition by Table_Schema, Table_Name
order by ORDINAL_POSITION) as RowNum
from INFORMATION_SCHEMA.COLUMNS
)
select @SQL = @SQL   case when RowNum = 1 then '' else ' union all ' end
  ' select '''   Column_Name   ''' as Column_Name, count(distinct '   quotename (Column_Name)   ' ) As DistinctCountValue, 
count( '  quotename (Column_Name)   ') as CountValue FROM '   quotename (Table_Schema)   '.'   quotename (Table_Name)
  @where
from cols
where Table_Name = 'table_name' --print @SQL

execute (@SQL)
  

Обратите внимание, что вам нужно будет экранировать одинарные кавычки в @where , если вы ищете строку. Например, declare @where nvarchar(max) = ' where state = ''CT''' .