我有一个Postgres表,其中包含来自不同制造商的产品数据,这里是简化的表结构:
CREATE TABLE test_table (
sku text,
manufacturer_name text,
price double precision,
stock int
);
INSERT INTO test_table
VALUES ('sku1', 'Manufacturer1', 110.00, 22),
('sku1', 'Manufacturer2', 120.00, 15),
('sku1', 'Manufacturer3', 130.00, 1),
('sku1', 'Manufacturer3', 30.00, 11),
('sku2', 'Manufacturer1', 10.00, 2),
('sku2', 'Manufacturer2', 9.00, 3),
('sku3', 'Manufacturer2', 21.00, 3),
('sku3', 'Manufacturer2', 1.00, 7),
('sku3', 'Manufacturer3', 19.00, 5);
我需要为每个sku输出每个制造商,但是如果同一sku有多个相同的制造商,则需要选择价格最低的制造商(请注意,我还需要包括“库存”列),此处为所需结果:
| sku | man1_price | man1_stock | man2_price | man2_stock | man3_price | man3_stock |
|------|------------|------------|------------|------------|------------|------------|
| sku1 | 110.0 | 22 | 120.0 | 15 | 30.0 | 11 |
| sku2 | 10.0 | 2 | 9.0 | 3 | | |
| sku3 | | | 1.0 | 7 | 19.0 | 5 |
我尝试使用Postgres crosstab()
:
SELECT *
FROM crosstab('SELECT sku, manufacturer_name, price
FROM test_table
ORDER BY 1,2',
$$ SELECT DISTINCT manufacturer_name FROM test_table ORDER BY 1 $$
)
AS ct (sku text, "man1_price" double precision,
"man2_price" double precision,
"man3_price" double precision
);
但这会产生一个只有一price
列的表。而且我没有找到包括该stock
列的方法。
我也尝试使用条件聚合:
SELECT sku,
MIN(CASE WHEN manufacturer_name = 'Manufacturer1' THEN price END) as man1_price,
MIN(CASE WHEN manufacturer_name = 'Manufacturer1' THEN stock END) as man1_stock,
MIN(CASE WHEN manufacturer_name = 'Manufacturer2' THEN price END) as man2_price,
MIN(CASE WHEN manufacturer_name = 'Manufacturer2' THEN stock END) as man2_stock,
MIN(CASE WHEN manufacturer_name = 'Manufacturer3' THEN price END) as man3_price,
MIN(CASE WHEN manufacturer_name = 'Manufacturer3' THEN stock END) as man3_stock
FROM test_table
GROUP BY sku
ORDER BY sku
而且此查询在我的情况下也不起作用-它仅选择最低库存水平-但如果相同sku的相同制造商很少,但价格/库存不同,则此查询从一个制造商中选择最低价格,从另一个。
如何从该表输出每个制造商的产品price
和对应的产品stock
?
PS谢谢大家这么有用的答案。我的Postgres的表是相当小-有不超过产品15K,(我不知道这样的数字可以进行适当的比较很有用),但由于欧文Brandstetter修改要求比较不同的查询性能我跑了3个查询用EXPLAIN ANALYZE
,这里是他们的执行时间处理时间:
Erwin Brandstetter query: 400 - 450 ms
Kjetil S query: 250 - 300 ms
Gordon Linoff query: 200 - 250 ms
a_horse_with_no_name query: 250 - 300 ms
再次-我不确定这些数字是否可以用作参考。就我而言,我选择了Kjetil S
和Gordon Linoff
查询的组合版本,但是Erwin Brandstetter
和a_horse_with_no_name
变体也非常有用和有趣。值得注意的是,如果将来我的表最终会拥有更多的制造商,那么-每次都要调整查询并键入他们的名字将很麻烦-因此,a_horse_with_no_name
答案中的查询将是最方便使用的查询。
您的最后选择几乎可以正常工作。但是您应该添加一个where条件,以删除每个制造商每sku非最低价格的行。这将产生您的预期结果:
select
sku,
min( case when manufacturer_name='Manufacturer1' then price end ) man1_price,
min( case when manufacturer_name='Manufacturer1' then stock end ) man1_stock,
min( case when manufacturer_name='Manufacturer2' then price end ) man2_price,
min( case when manufacturer_name='Manufacturer2' then stock end ) man2_stock,
min( case when manufacturer_name='Manufacturer3' then price end ) man3_price,
min( case when manufacturer_name='Manufacturer3' then stock end ) man3_stock
from test_table t
where not exists (
select 1 from test_table
where sku=t.sku
and manufacturer_name=t.manufacturer_name
and price<t.price
)
group by sku
order by 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句