server 2008中复杂的平均计算

Question

sql server 2008

我有一个表，在该表中，我将根据所有列计算value列中可用数据的百分比。

现在，我正在试图计算值列中可用数据的avg百分比。

但挑战是，在计算所有avg列的时，我需要排除这些类型的列，例如

如果牙科为NULL，而DENTAL_VALUE为NULL，则在计算平均值时不应考虑。

但是，如果牙科不是NULL，而DENTAL_VALUE是NULL，那么计算中将包括这些内容。

下面的脚本给出了每个列的avg，

请任何人帮助我如何计算的总体平均为上述情况。

在这种情况下，在计算总体平均值时，我必须排除牙科和dental_value，但不排除肾。

表格

DECLARE @PHARMA TABLE
(
MRN VARCHAR (30),
department VARCHAR (30),
optical VARCHAR (30),
optical_value VARCHAR (30), 
dermo VARCHAR (30),
dermo_value VARCHAR (30),
Neuro VARCHAR (30),
Neuro_value VARCHAR (30),
DENTAL VARCHAR (30),
DENTAL_VALUE VARCHAR (30)
   Nephro VARCHAR (30),
    Nephro_VALUE VARCHAR (30)
)

Insert @PHARMA

SELECT 'BSE23098', 'Clinic', 'Willmar','10', 'Betamethasone' ,'20', 'neurobion', NULL,NULL,NULL,'TYPE-A',NULL UNION ALL

SELECT 'ZSE23098', 'Clinic', 'AUROBRIME','10', 'adapalene' ,'20', 'Blong', NULL,NULL,NULL,'TYPE-B',NULL UNION ALL
SELECT 'ZSE23098', 'Clinic', 'VOZOLE', NULL, 'SURFRAZ', '30' ,'Evion', '63' ,NULL,NULL,'TYPE-C',NULL

查询：

SELECT 
    CAST(optical_not_NULL * 100.0 / NULLIF((optical_NULL + optical_not_NULL), 0) AS decimal(5, 2)) AS optical_fill_rate,
    CAST(dermo_not_NULL * 100.0 / NULLIF((dermo_NULL + dermo_not_NULL), 0) AS decimal(5, 2)) AS dermo_fill_rate,
    CAST(dental_not_NULL * 100.0 / NULLIF((dental_NULL + dental_not_NULL), 0) AS decimal(5, 2)) AS dental_fill_rate,
    CAST(neuro_not_NULL * 100.0 / NULLIF((neuro_NULL + neuro_not_NULL), 0) AS decimal(5, 2)) AS neuro_fill_rate
FROM (
    SELECT
        SUM(CASE WHEN optical_value IS NULL THEN 1 ELSE 0 END) AS optical_NULL,
        SUM(CASE WHEN optical_value IS NULL THEN 0 ELSE 1 END) AS optical_not_NULL,
        SUM(CASE WHEN dermo_value IS NULL THEN 1 ELSE 0 END) AS dermo_NULL,
        SUM(CASE WHEN dermo_value IS NULL THEN 0 ELSE 1 END) AS dermo_not_NULL,
        SUM(CASE WHEN neuro_value IS NULL THEN 1 ELSE 0 END) AS neuro_NULL,
        SUM(CASE WHEN neuro_value IS NULL THEN 0 ELSE 1 END) AS neuro_not_NULL,
        SUM(CASE WHEN dental_value IS NULL THEN 1 ELSE 0 END) AS dental_NULL,
        SUM(CASE WHEN dental_value IS NULL THEN 0 ELSE 1 END) AS dental_not_NULL

    FROM @PHARMA
) AS derived

Answer 1

你可以简化成

SELECT  CAST (COUNT (CASE WHEN optical is not null 
                          THEN optical_value END)
              * 100.0 / COUNT(*) AS decimal(5,2) ) as optical_fill_rate
FROM    @PHARMA