为2021年每个月出现频率最高的值

Question

试图获取每个月出现频率最高的值从表

检查表:

CREATE TABLE inspection (lno INT,
idate DATE,
iid INT,
stime TIME,
passed INT,
violations VARCHAR(100),
check (passed = 1 or passed = 0),
PRIMARY KEY(lno,idate),
FOREIGN  key (lno) REFERENCES restaurant);

可以忽略->外键(lno)引用餐厅

数据:

INSERT INTO inspection VALUES
(234,'6.1.2020' ,333, '16:00', 1 ,NULL),
(123,'7.2.2020' ,333 ,'12:15' ,0 ,'rats'),
(234, '7.2.2020', 333, '17:00', 0, 'Bugs'),
(456, '1.3.2021' ,222, '20:00' ,1,NULL),
(234, '10.3.2021', 333, '16:00', 1,NULL),
(567, '24.3.2021' ,333, '17:00' ,1,NULL),
(345, '9.4.2021' ,222, '18:00', 0, 'Rats'),
(345, '30.4.2021' ,222, '18:00' ,1,NULL),
(123,'11.5.2021', 111, '19:40', 0 ,'Mold'),
(567, '15.5.2021' ,111 ,'19:00' ,1,NULL),
(345, '17.5.2021' ,222, '19:00' ,1,NULL),
(456, '19.5.2021', 111 ,'17:00', 0 ,'Bats'),
(123, '13.6.2021' ,222, '13:00', 1,NULL),
(456, '16.6.2021' ,333 ,'21:00' ,0 ,'Mold');

查询:

SELECT date_part('month', idate) ,max(iid)
FROM inspector natural join inspection where date_part('year', idate) >=  date_part('year', current_date)
GROUP BY date_part('month', idate) 

输出:

<表类>月idtbody><<tr>3333422252226333

恕我直言，您不需要检查器表进行此计算。像这样的查询:

with t1(month, iid, cnt) as 
(
select date_part('month', idate), iid, count(*) 
from inspection
where date_part('year', idate) = date_part('year',current_date)
group by date_part('month', idate), iid
),
t2 (month, maxCnt) as 
(
select month, max(cnt)
from t1
group by month
)
select t1.month, t1.iid 
from t1 
inner join t2 on t1.month = t2.month and t1.cnt = t2.maxCnt
order by t1.month, t1.iid;

这是Dbfiddle演示链接。

这是一个不使用连接的方法。在DATE_PART和RANK的辅助下

WITH occurrences AS (
SELECT
DATE_PART('MONTH',idate) as month,
iid, 
COUNT(iid) cnt
FROM
inspection
WHERE 
DATE_PART('YEAR',idate)=2021
GROUP BY
DATE_PART('MONTH',idate),
iid
),
ranked AS (
SELECT 
month,
iid,
RANK() OVER (PARTITION BY month ORDER BY cnt DESC) rnk
FROM 
occurrences
)
SELECT
month,
iid
FROM 
ranked
WHERE
rnk=1

DB小提琴