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

概述结合窗口函数(如OVER(PARTITION BY id))可以计算不同的值吗？目前我的查询如下： SELECT congestion.date, congestion.week_nb, congestion.id_congestion, congestion.id_element,ROW_NUMBER() OVER( PARTITION BY congestion.id_elem 结合窗口函数(如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_countFROM congestionWHERE 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 congestionWHERE congestion.date >= '2014.01.01'AND congestion.date <= '2014.12.31' GROUP BY congestion.ID_element)SELECT congestion.date,uniques.unique_references AS week_countFROM congestionJOIN 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_countFROM congestionWHERE congestion.date >= '2014.01.01'AND congestion.date <= '2014.12.31'ORDER BY ID_element,date

总结

以上是内存溢出为你收集整理的postgresql – 使用OVER(PARTITION BY id)计数不同的值全部内容，希望文章能够帮你解决postgresql – 使用OVER(PARTITION BY id)计数不同的值所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。