多个条件下的计数平均值

Question

我正在尝试创建一个查询，该查询允许对每月特定数据的平均百分比进行分类。

我的数据集是这样呈现的:

<表类> 日期 名称 组 % tbody><<tr>2022-01-21name1gr1一起5.22022-01-22name1gr1一起6.12022-01-26name1gr1一起4.92022-02-01name1gr1一起3.22022-02-03name1gr1一起8.12022-01-22name2gr1一起36.12022-01-25name2gr1一起32.12022-02-10name2gr1一起35.8…………

根据您的示例，该查询产生所需的结果。所以基本上这结合了你的两个查询使用子查询，其中子查询负责计算由Name,Month和Group分组的AVG，外部查询是COUNT和"分类">

SELECT
Month,
COUNT(CASE
WHEN avg <= 25 THEN Name
END) AS _25,
COUNT(CASE
WHEN avg  > 25
AND avg  <= 50 THEN Name
END) AS _50,
COUNT(CASE
WHEN avg  > 50
AND avg  <= 75 THEN Name
END) AS _75,
COUNT(CASE
WHEN avg  > 75
AND avg <= 100 THEN Name
END) AS _100
FROM
(
SELECT
EXTRACT(MONTH from Date) AS Month,
Name,
AVG(Percent) AS avg
FROM
table1
GROUP BY Month, Name, Group
HAVING Group = 'gr1'
) AS namegr
GROUP BY Month

结果如下:

<表类>月_25_50_75_100tbody><<tr>1110021100

您可以使用此查询分组月和每个Name

SELECT CONCAT(EXTRACT(MONTH FROM Date), ', ', Name) AS DateAndName, 
CASE
WHEN AVG(Percent) <= 25 THEN '1'
ELSE '0'
END AS '<=25%',
CASE
WHEN AVG(Percent) > 25 AND AVG(Percent) <= 50 THEN '1'
ELSE '0'
END AS '25<_<=50%',
CASE
WHEN AVG(Percent) > 50 AND AVG(Percent) <= 75 THEN '1'
ELSE '0'
END AS '50<_<=75%',
CASE
WHEN AVG(Percent) > 75 AND AVG(Percent) <= 100 THEN '1'
ELSE '0'
END AS '75<_<=100%'
from DataTable /*change to your table name*/
group by EXTRACT(MONTH FROM Date), Name
order by DateAndName

它给出如下结果:

DateAndName& lt; = 25%25 & lt; _<= 50%50 & lt; _<= 75%75 & lt; _<= 100%0000