我正在写一个查询来汇总Postgres数据库中的数据:
SELECT products.id,
products.NAME,
product_types.type_name AS product_type,
delivery_types.delivery,
products.required_selections,
Count(s.id) AS selections_count,
Sum(CASE
WHEN ss.status = 'WARNING' THEN 1
ELSE 0
END) AS warning_count
FROM products
JOIN product_types
ON product_types.id = products.product_type_id
JOIN delivery_types
ON delivery_types.id = products.delivery_type_id
LEFT JOIN selections_products sp
ON products.id = sp.product_id
LEFT JOIN selections s
ON s.id = sp.selection_id
LEFT JOIN selection_statuses ss
ON ss.id = s.selection_status_id
LEFT JOIN listings l
ON ( s.listing_id = l.id
AND l.local_date_time BETWEEN
To_timestamp('2014/12/01', 'YYYY/mm/DD'
) AND
To_timestamp('2014/12/30', 'YYYY/mm/DD') )
GROUP BY products.id,
product_types.type_name,
delivery_types.delivery
基本上,我们有一个带有选择项的产品,这些选择项有清单,而清单中有一个local_date
。我需要所有产品的清单,以及两个日期之间有多少清单。无论我做什么,我都会统计所有选择(总计)。我觉得自己正在忽略某些东西。同样的概念也适用warning_count
。另外,我不太了解为什么Postgres要求我在group by
此处添加一个。
模式如下所示(无论如何,您都会关心的部分):
products
name:string
, product_type:fk
, required_selections:integer
, deliver_type:fk
selections_products
product_id:fk
, selection_id:fk
selections
selection_status_id:fk
, listing_id:fk
selection_status
status:string
listing
local_date:datetime
您拥有的方式,无论您LEFT JOIN
选择什么,都可以选择listings.local_date_time
。
有解释的空间,我们需要查看包含所有约束和数据类型的实际表定义,以确保确定。大胆尝试一下,我有根据的猜测是,您可以在FROM
子句中使用括号来固定连接的优先级,从而解决查询问题:
SELECT p.id
, p.name
, pt.type_name AS product_type
, dt.delivery
, p.required_selections
, count(s.id) AS selections_count
, sum(CASE WHEN ss.status = 'WARNING' THEN 1 ELSE 0 END) AS warning_count
FROM products p
JOIN product_types pt ON pt.id = p.product_type_id
JOIN delivery_types dt ON dt.id = p.delivery_type_id
LEFT JOIN ( -- LEFT JOIN!
selections_products sp
JOIN selections s ON s.id = sp.selection_id -- INNER JOIN!
JOIN listings l ON l.id = s.listing_id -- INNER JOIN!
AND l.local_date_time >= '2014-12-01'
AND l.local_date_time < '2014-12-31'
LEFT JOIN selection_statuses ss ON ss.id = s.selection_status_id
) ON sp.product_id = p.id
GROUP BY p.id, pt.type_name, dt.delivery;
这样一来,你先消除在规定的时间框架之外的所有选择与[INNER] JOIN
之前你LEFT JOIN
要的产品,从而保持所有产品的结果,包括那些没有任何可适用的选择。
有关的:
选择全部或大多数产品时,可以将其重写为更快:
SELECT p.id
, p.name
, pt.type_name AS product_type
, dt.delivery
, p.required_selections
, COALESCE(s.selections_count, 0) AS selections_count
, COALESCE(s.warning_count, 0) AS warning_count
FROM products p
JOIN product_types pt ON pt.id = p.product_type_id
JOIN delivery_types dt ON dt.id = p.delivery_type_id
LEFT JOIN (
SELECT sp.product_id
, count(*) AS selections_count
, count(*) FILTER (WHERE ss.status = 'WARNING') AS warning_count
FROM selections_products sp
JOIN selections s ON s.id = sp.selection_id
JOIN listings l ON l.id = s.listing_id
LEFT JOIN selection_statuses ss ON ss.id = s.selection_status_id
WHERE l.local_date_time >= '2014-12-01'
AND l.local_date_time < '2014-12-31'
GROUP BY 1
) s ON s.product_id = p.id;
首先汇总并计算选择和警告product_id
,然后再加入产品,这样比较便宜。(除非只检索一小部分产品,否则先减少相关行会比较便宜。)
有关的:
另外,我不太了解为什么Postgres要求我在此处添加一个小组。
从Postgres 9.1开始,PK列GROUP BY
覆盖同一表的所有列。这并不能掩盖列其他表,即使他们在功能上是相关的。GROUP BY
如果您不想汇总它们,则需要在其中明确列出。
我的第二个查询从一开始就通过在联接之前进行聚合来避免此问题。
撇开:机会是,这并不能满足您的要求:
l.local_date_time BETWEEN To_timestamp('2014/12/01', 'YYYY/mm/DD')
AND To_timestamp('2014/12/30', 'YYYY/mm/DD')
由于date_time
似乎是类型timestamp
(不是timestamptz
!),因此您应添加“ 2014-12-30 00:00”,但不包括一天的其余时间“ 2014-12-30”。对于日期和时间戳,最好使用ISO 8601格式,这与每个语言环境和datestyle
设置都相同。因此:
WHERE l.local_date_time >= '2014-12-01'
AND l.local_date_time < '2014-12-31'
其中包括所有“ 2014-12-30”,仅此而已。不知道为什么您选择排除“ 2014-12-31”。也许您真的想包括2014年12月的全部内容?
WHERE l.local_date_time >= '2014-12-01'
AND l.local_date_time < '2015-01-01'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句