postgresql - 在 PostgreSQL 中聚合和连接两个表
问题描述
我正在尝试使用来自两个不同表的聚合来生成聚合输出表。我不清楚如何加入这两个结果。这两个表,一个列出每个商店中的所有产品,另一个列出每个产品的价格变化如下所示。
| product_id | daily_price | date |
|------------|-------------|------------|
| 1 | 1.25$ | 01-01-2000 |
| 1 | ... | ... |
| 1 | 1$ | 31-12-2000 |
| 2 | 4.5$ | 01-01-2000 |
| 2 | ... | ... |
| 2 | 4.25$ | 31-12-2000 |
| store_id | product_id |
|----------|------------|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
第一个聚合获得所有产品的平均每日价格(它会有所不同)。
SELECT product_id, ROUND((AVG(price)),2) as average_price FROM product_dailyprices
GROUP BY product_id;
| product_id | average_price |
|------------|---------------|
| 1 | 50 |
| 2 | 100 |
| 3 | 250 |
第二个查询让我知道每个商店中可用的不同产品的数量
SELECT store, COUNT(product_id) as product_count FROM products
GROUP BY store;
| store_id | product_count |
|----------|---------------|
| 1 | 200 |
| 2 | 250 |
| 3 | 225 |
我对如何执行查询以产生以下内容有点迷茫:
| store_id | product_count | average_price_at_store |
|----------|---------------|------------------------|
| 1 | 34 | 6.51$ |
| 2 | 45 | 3.23$ |
| 3 | 36 | 5.37$ |
谢谢您的帮助!
解决方案
由于您没有为表提供 SQL,因此让我们使用以下裸骨结构:
CREATE TABLE products
(
id SERIAL NOT NULL,
name text NOT NULL,
CONSTRAINT products_pk PRIMARY KEY (id)
);
CREATE TABLE stores
(
id SERIAL NOT NULL,
name text NOT NULL,
CONSTRAINT stores_pk PRIMARY KEY (id)
);
CREATE TABLE daily_prices
(
product_id INTEGER NOT NULL,
daily_price DOUBLE PRECISION NOT NULL,
date timestamptz,
CONSTRAINT daily_prices_product FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE locations
(
store_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
CONSTRAINT products_product_fk FOREIGN KEY (product_id) REFERENCES products (id),
CONSTRAINT products_store_fk FOREIGN KEY (store_id) REFERENCES stores (id)
);
让我们输入一些样本数据来帮助验证查询是否有效:
INSERT INTO products(name)
VALUES ('product 1');
INSERT INTO products(name)
VALUES ('product 2');
INSERT INTO products(name)
VALUES ('product 3');
INSERT INTO stores(name)
VALUES ('store 1');
INSERT INTO stores(name)
VALUES ('store 2');
insert into locations (store_id, product_id)
values (1, 1),
(1, 2),
(2, 2),
(2, 3);
INSERT INTO daily_prices(product_id, daily_price, date)
VALUES (1, 2.0, '01-01-2020');
INSERT INTO daily_prices(product_id, daily_price, date)
VALUES (1, 4.0, '02-01-2020');
INSERT INTO daily_prices(product_id, daily_price, date)
VALUES (2, 3.0, '01-01-2020');
INSERT INTO daily_prices(product_id, daily_price, date)
VALUES (2, 5.0, '02-01-2020');
INSERT INTO daily_prices(product_id, daily_price, date)
VALUES (3, 10.0, '01-01-2020');
INSERT INTO daily_prices(product_id, daily_price, date)
VALUES (3, 20.0, '02-01-2020');
然后生成所需表的查询如下所示:
select l.store_id as store_id,
count(distinct l.product_id) as number_of_products,
avg(dp.daily_price) as average_price
from locations l
join daily_prices dp on dp.product_id = l.product_id
group by l.store_id;
我们可以手动验证它是否计算出预期的结果:
+--------+------------------+-------------+
|store_id|number_of_products|average_price|
+--------+------------------+-------------+
|1 |2 |3.5 |
|2 |2 |9.5 |
+--------+------------------+-------------+
推荐阅读
- unity3d - 使用 MRTK 的 Unity Light cookie
- c# - 如何在 Visual Studio 中一次运行使用 Restharp 和 Web 应用程序编写的 xUnit 集成测试?
- android - chrome 80 samesite cookie 默认更改 - android webview
- import - 可传输表空间、DataPump、Golden Gate 和带有 DB_Link 的视图哪个最好?
- android - Android 获取调用类字段
- azure-devops - 使用 Azure Pipelines 从 nuspec 构建 nuget 文件
- css - 无法在中心显示材料 ui 小吃吧
- android - 无法将 android 平台添加到 ionic 项目
- jenkins - 除了 UI,Blue Ocean for Jenkins 与 Jenkins 管道插件相比还有哪些其他优点?
- r - 澄清由 `gargle` 包生成的 .RDS OAuth 令牌