postgresql – 使用OVER(PARTITION BY id)计数不同的值

发布时间：2020-12-13 16:33:33 所属栏目：百科来源：网络整理

导读：结合窗口函数(如OVER(PARTITION BY id))可以计算不同的值吗？目前我的查询如下： SELECT congestion.date,congestion.week_nb,congestion.id_congestion,congestion.id_element,ROW_NUMBER() OVER( PARTITION BY congestion.id_element ORDER BY congestion.

结合窗口函数(如OVER(PARTITION BY id))可以计算不同的值吗？目前我的查询如下：

SELECT congestion.date,congestion.week_nb,congestion.id_congestion,congestion.id_element,ROW_NUMBER() OVER(
    PARTITION BY congestion.id_element
    ORDER BY congestion.date),COUNT(DISTINCT congestion.week_nb) OVER(
    PARTITION BY congestion.id_element
) AS week_count
FROM congestion
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
ORDER BY id_element,date

但是,当我尝试执行查询时,我会收到以下错误：

"COUNT(DISTINCT": "DISTINCT is not implemented for window functions"

否,正如错误消息所述,DISTINCT未实现与Windows功能.从 this link到您的情况下,您可以使用以下内容：

WITH uniques AS (
 SELECT congestion.id_element,COUNT(DISTINCT congestion.week_nb) AS unique_references
 FROM congestion
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
 GROUP BY congestion.id_element
)

SELECT congestion.date,uniques.unique_references AS week_count
FROM congestion
JOIN uniques USING (id_element)
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
ORDER BY id_element,date

根据情况,您还可以将子查询直接放入SELECT列表中：

SELECT congestion.date,(SELECT COUNT(DISTINCT dist_con.week_nb)
    FROM congestion AS dist_con
    WHERE dist_con.date >= '2014.01.01'
    AND dist_con.date <= '2014.12.31'
    AND dist_con.id_element = congestion.id_element) AS week_count
FROM congestion
WHERE congestion.date >= '2014.01.01'
AND congestion.date <= '2014.12.31'
ORDER BY id_element,date

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!