大佬教程收集整理的这篇文章主要介绍了在 Postgres 中使用 COUNT 和聚合,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在运行以下查询来检索我的数据:
SELECT
e.ID AS ID,e.name AS name,e.description AS description,c.slug AS category,COUNT(t.ID) AS sold,Json_agg(Json_build_object('ID',b.ID,'title',b.title,'description',b.description,'price',b.price,'available',b.qty_available,'qty_per_SALE',b.qty_per_SALE))::JSONB AS book,s.ID,'startDate',s.start_date,'endDate',s.END_DATE,'daysAhead',(s.start_date::DATE - Now()::DATE),'times',s.times))::JSONB as dates
FROM event e
left JOIN books b ON b.event_ID = e.ID
left JOIN shows s ON s.event_ID = e.ID
left JOIN category c ON e.category_ID = c.ID
left JOIN ticket t ON t.book_ID = b.ID
WHERE
(status = 'PUBliSHED' OR status = 'PROMOTED')
AND s.END_DATE >= DATE(Now())
AND e.is_private = falSE
AND s.ID = t.show_ID
AND t.canceled = falSE
GROUP BY e.ID,c.slug
ORDER BY sold
liMIT 30
这个查询工作正常。我得到事件级聚合 sold
。
table ticket (
ID seriaL PRIMary KEY,book_ID seriaL REFERENCES books(ID),order_ID seriaL REFERENCES purchases(ID),show_ID seriaL REFERENCES shows(ID),showtime character varying(10) NOT NULL,canceled Boolean DEFAulT false
);
我想在日期内向此报告添加另一个 sold
属性。目前,日期条目看起来像:
[
{
"ID": 46,"times": [
{
"end": "13:00","start": "12:00"
}
],"endDate": "2022-02-27","daysAhead": 308,"startDate": "2022-02-27"
},{
"ID": 46,"startDate": "2022-02-27"
}
]
我想聚合等于 time.start
的票务显示时间,因此除了开始和结束之外,每个时间元素看起来都具有这个新的 sold
属性。
我尝试使用 POSTGRes WITH
将当前查询转换为一个集合,然后再次JOIN
ticket
表在它上面,但到目前为止没有太大成功。
基本上,我希望将时间分开并为它添加一个属性,例如每个事件的 COUNT(t.ID),仅在每个节目中过滤。
但是,不允许嵌套聚合,COUNT() 为 1。
有什么建议吗?
与其将当前查询转换为 with
语句并再次加入票证,不如换一种方式更容易,即在 with
子句中使用票证表并将其加入到当前查询。根据您的描述,我不确定您希望输出是什么样的,但可能类似于:
WITH ticket_sumMary as(
SELEct book_id,count(1) as ticket_count
from ticket
group by book_id
)
SELECT
e.id AS id,e.name AS name,e.description AS description,c.slug AS category,COUNT(t.id) AS sold,json_agg(json_build_object('id',b.id,'title',b.title,'description',b.description,'price',b.price,'available',b.qty_available,'qty_per_SALE',b.qty_per_SALE,'SALEs',ts.ticket_count))::JSONB AS book,--can now use in aggregate
json_agg(json_build_object('id',s.id,'startDate',s.start_date,'endDate',s.END_DATE,'daysAhead',(s.start_date::DATE - NOW()::DATE),'times',s.times))::JSONB as dates
FROM event e
LEFT JOIN books b ON b.event_id = e.id
LEFT JOIN shows s ON s.event_id = e.id
LEFT JOIN category c ON e.category_id = c.id
LEFT JOIN ticket t ON t.book_id = b.id
--pull in sumMary data
LEFT JOIN ticket_sumMary ts ON ts.book_id = b.id
WHERE
(status = 'PUBLISHED' OR status = 'PROMOTED')
AND s.END_DATE >= DATE(NOW())
AND e.is_private = falSE
AND s.id = t.show_id
AND t.canceled = falSE
GROUP BY e.id,c.slug
ORDER BY sold
LIMIT 30
以上是大佬教程为你收集整理的在 Postgres 中使用 COUNT 和聚合全部内容,希望文章能够帮你解决在 Postgres 中使用 COUNT 和聚合所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。