SQL统计值大于0的列数及新增对应计算字段技术咨询

Alright, let's break down how to tackle both of your SQL problems using your sample table structure (Account_number, DAY_0, DAY_30, DAY_60, DAY_90, DAY_120) and example data (acc_001 with values 99, 10, 0, 0.2, 0) as reference.

需求1：统计值大于0的列的相关数量 #

I'll cover two common scenarios here—pick the one that matches your actual need:

场景A：统计每个数值列中值大于0的行数 #

If you want to know how many records in each DAY_* column have a value greater than 0, use aggregate functions with CASE statements:

SELECT
  SUM(CASE WHEN DAY_0 > 0 THEN 1 ELSE 0 END) AS day_0_positive_count,
  SUM(CASE WHEN DAY_30 > 0 THEN 1 ELSE 0 END) AS day_30_positive_count,
  SUM(CASE WHEN DAY_60 > 0 THEN 1 ELSE 0 END) AS day_60_positive_count,
  SUM(CASE WHEN DAY_90 > 0 THEN 1 ELSE 0 END) AS day_90_positive_count,
  SUM(CASE WHEN DAY_120 > 0 THEN 1 ELSE 0 END) AS day_120_positive_count
FROM your_table_name;

For your sample data (only the acc_001 row), this query returns:
day_0_positive_count=1, day_30_positive_count=1, day_60_positive_count=0, day_90_positive_count=1, day_120_positive_count=0.

场景B：统计表中有多少个数值列存在至少一个大于0的值 #

If you want to count how many DAY_* columns have at least one record with a value greater than 0, use a subquery with UNION ALL:

SELECT COUNT(*) AS positive_column_count
FROM (
  SELECT 'DAY_0' AS col_name FROM your_table_name WHERE DAY_0 > 0 LIMIT 1
  UNION ALL
  SELECT 'DAY_30' AS col_name FROM your_table_name WHERE DAY_30 > 0 LIMIT 1
  UNION ALL
  SELECT 'DAY_60' AS col_name FROM your_table_name WHERE DAY_60 > 0 LIMIT 1
  UNION ALL
  SELECT 'DAY_90' AS col_name FROM your_table_name WHERE DAY_90 > 0 LIMIT 1
  UNION ALL
  SELECT 'DAY_120' AS col_name FROM your_table_name WHERE DAY_120 > 0 LIMIT 1
) AS cols_with_positive;

For your sample data, this returns positive_column_count=3 (since DAY_0, DAY_30, and DAY_90 have values > 0).

需求2：新增计算字段统计当前行中值大于0的列总数 #

To add a temporary calculated field (or a permanent one) that counts how many DAY_* columns have values > 0 for each row:

临时查询字段（仅在查询结果中显示） #

Use CASE statements summed together to get the count per row:

SELECT
  Account_number,
  DAY_0,
  DAY_30,
  DAY_60,
  DAY_90,
  DAY_120,
  (
    CASE WHEN DAY_0 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_30 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_60 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_90 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_120 > 0 THEN 1 ELSE 0 END
  ) AS positive_column_total
FROM your_table_name;

For your acc_001 row, positive_column_total will be 3 (since 99, 10, and 0.2 are greater than 0).

永久新增字段到表中（自动同步更新） #

If you want to add this field permanently to the table (works for MySQL; adjust syntax slightly for other databases like PostgreSQL/SQL Server):

ALTER TABLE your_table_name
ADD COLUMN positive_column_total INT GENERATED ALWAYS AS (
    CASE WHEN DAY_0 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_30 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_60 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_90 > 0 THEN 1 ELSE 0 END +
    CASE WHEN DAY_120 > 0 THEN 1 ELSE 0 END
) STORED;

This field will auto-calculate values for existing rows and update automatically when you modify data in the DAY_* columns.

内容的提问来源于stack exchange，提问作者irfan3012