如何找到记录超过3的列数？

Question

为了解决这个问题，我从information_schema.columns中获取列的名称，并进行查询，其中应该计算一列中的记录数。但是该子查询返回多个值，我不知道为什么，因为它应该返回行数-一个值。请解释我做错了什么。

SELECT count(COLUMN_NAME)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'users' group by COLUMN_NAME having((select count(COLUMN_NAME) from users where COLUMN_NAME is not null)>3) 

列

   name | surname
__________________
    a   | b
    c   | d
    e   | f
    e   | null

函数应该返回1 (name列有4条记录)

Answer 1

您可以尝试此解决方案：

DECLARE dsql STRING;
DECLARE qry STRING;
SET qry = (
    SELECT
      STRING_AGG(ARRAY_TO_STRING(["SELECT '", column_name, "' AS ColumnName, " || "COUNT(" || column_name || ") AS RowCount FROM mydataset.", table_name, ' union all'], ''), ' ')
    FROM
      mydataset.INFORMATION_SCHEMA.COLUMNS
    WHERE
      table_name = 'users' );
SET dsql = 'SELECT * FROM ( ' || SUBSTR(qry, 1, LENGTH(qry) - LENGTH(' union all')) || ') WHERE RowCount > 3';
EXECUTE IMMEDIATE dsql;