sql - 计算客户购买商品的平均价格
问题描述
我有三个表:客户、订单和订单项。它们的设置如下:
CREATE TABLE cust_account(
cust_id DECIMAL(10) NOT NULL,
first VARCHAR(30),
last VARCHAR(30),
address VARCHAR(50),
PRIMARY KEY (cust_id));
CREATE TABLE orders(
order_num DECIMAL(10) NOT NULL,
cust_id DECIMAL(10) NOT NULL,
order_date DATE,
PRIMARY KEY (order_num));
CREATE TABLE lines(
order_num DECIMAL(10) NOT NULL,
line_id DECIMAL(10) NOT NULL,
item_num DECIMAL(10) NOT NULL,
price DECIMAL(10),
PRIMARY KEY (order_id, line_id),
FOREIGN KEY (item_id) REFERENCES products);
使用 Oracle,我需要编写一个查询,显示购买超过 5 次或更多的客户的平均商品价格。这是我一直在使用的:
SELECT DISTINCT cust_account.cust_id,cust_account.first, cust_account.last, lines.AVG(price) AS average_price
FROM cust_account
JOIN orders
ON cust_account.cust_id = orders.cust_id
JOIN lines
ON lines.order_num = orders.order_num
WHERE lines.item_num IN (SELECT lines.item_num
FROM lines
JOIN orders
ON lines.order_num = orders.order_num
GROUP BY lines.order_num
HAVING COUNT(DISTINCT orders.cust_id) >= 5
);
解决方案
- ...
INNER JOIN
你所有的桌子在一起 - ...
GROUP BY
客户并计算每个客户行的平均价格 - ...使用一个
HAVING
子句将结果限制为购买 5 次或更多的组
由于这闻起来像家庭作业,所以我会在长时间的停顿后发布完整的答案......
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
SELECT ca.first, ca.last, avg(l.price) avg_price
FROM cust_account ca
INNER JOIN orders o ON o.cust_id = ca.cust_id
INNER JOIN lines l ON l.order_num = o.order_number
GROUP BY ca.first, ca.last
HAVING COUNT(distinct l.line_id) >=5
-- OR, maybe your requirement is ...
-- HAVING COUNT(distinct o.order_num) >= 5
-- ... the question was a bit unclear on this point
推荐阅读
- r - 将两个数据框与公共列连接在一起
- reactjs - Stripe 应该集成在前端还是后端?(反应+Django 休息)
- vb.net - 与主表单中的“标签”属性一起使用时,类列表会出错
- javascript - React Native Navigator Stack Screen 选项未定义?
- javascript - 在循环中计算分数的总和
- python - 气流 - alembic.util.exc.CommandError:找不到由“a13f7613ad25”标识的修订版
- wpf - ScrollViewer (VerticalScrollBar) 中的垂直条不与用户控件一起显示,如 WPF XAML 中的扩展器
- qiskit - 没有名为“qiskit_metal”的模块
- java - Jave,报告不显示碧玉报告
- javascript - 使用 async-await 按特定顺序执行 javascript 函数