mysql - 编写一个 SQL 查询来获取所有预订及其价格、提货位置、送货地点和价格差异

Question

给定表 booking 和表 payment_log，知道可能会发生发货更新并记录到 payment_log 表中，编写一个 SQL 查询来获取所有预订及其价格、提货位置、交货位置和价格差异。表说明:

CREATE TABLE booking (
booking_id integer,
price numeric,
PRIMARY KEY (booking_id)
); 


CREATE TABLE shipment_log (
log_id integer NOT NULL,
booking_id integer,
pickup_city varchar,
pickup_state varchar,
delivery_city varchar,
delivery_state varchar,
"timestamp" timestamp,
price numeric,
PRIMARY KEY (log_id),
CONSTRAINT FK_booking FOREIGN KEY (booking_id)
REFERENCES booking(booking_id));

我的查询是

SELECT DISTINCT booking.booking_id,
booking.price,shipment_log.pickup_city,
shipment_log.pickup_state,shipment_log.delivery_city,
shipment_log.delivery_state
FROM booking, shipment_log
WHERE booking.booking_id = shipment_log.booking_id
AND booking.price = shipment_log.price;    

但我无法计算此查询中的价格差异

单独查询方差:

SELECT  VARIANCE(price)           
FROM shipment_log
GROUP By booking_id;

任何人都可以帮我为此编写一个嵌套查询吗？或者我应该如何尝试获得所需的输出？

我已从 csv 文件导入数据。数据:(shipping_log的数据很大，因此我附上了一些样本)

enter code here

booking: 
booking_id  price
1   1350
2   1600
3   4700
4   550
5   6000
6   5000
7   2500
8   6500
9   3000
10  3100
11  6000
12  425
13  1000
14  950
15  3000
16  1950
17  5600
18  3000
19  3500
20  2100
21  5000
22  5600
23  550
24  2200
25  5300
26  1000
27  5000
28  1000

发货日志:

enter code here
log_id  booking_id  pickup_city pickup_state    delivery_city    
delivery_state  log_timestamp   price
75095244    1   Akron   OH  Statesville NC  2018-03-09 15:03:36.491+00  1350
75097084    1   Akron   OH  Statesville NC  2018-03-07 20:06:00.874+00  1450
75096376    1   Akron   OH  Statesville NC  2018-03-02 23:31:26.025+00  1400
75095248    1   Akron   OH  Statesville NC  2018-02-23 21:37:54.65+00   1400
75097140    1   Akron   OH  Statesville NC  2018-02-23 21:35:41.283+00  1225
75097252    1   Akron   OH  Statesville NC  2018-02-19 13:39:12.514+00  1400
75094692    1   Akron   OH  Statesville NC  2018-02-19 13:35:09.376+00  1300
75096388    1   Akron   OH  Statesville NC  2018-02-16 15:49:19.613+00  1500
75096440    1   Akron   OH  Statesville NC  2018-02-09 22:10:01.971+00  1100
75094728    1   Akron   OH  Statesville NC  2018-02-09 15:51:15.866+00  1500
75096432    1   Akron   OH  Statesville NC  2018-02-05 14:05:49.521+00  1100
75094764    1   Akron   OH  Statesville NC  2018-02-02 16:58:53.096+00  1610
75096500    1   Akron   OH  Statesville NC  2018-01-27 15:52:46.319+00  1100
75094820    1   Akron   OH  Statesville NC  2018-01-19 20:18:36.112+00  1750
75096580    1   Akron   OH  Statesville NC  2018-01-19 20:16:13.837+00  1700
75095552    1   Akron   OH  Statesville NC  2018-01-19 20:08:18.841+00  2000
75096336    2   Brighton    MI  Statesville NC  2018-03-02 22:43:11.297+00   
1600
75095356    2   Brighton    MI  Statesville NC  2018-02-22 18:25:48.821+00   
1600
75097284    2   Brighton    MI  Statesville NC  2018-02-17 14:15:00.722+00   
1750
75094744    2   Brighton    MI  Statesville NC  2018-02-09 19:34:53.796+00   
1600
75095528    2   Brighton    MI  Statesville NC  2018-01-26 15:10:16.254+00   
1600
75097436    2   Brighton    MI  Statesville NC  2018-01-26 14:17:53.284+00   
1600
75096812    2   Brighton    MI  Statesville NC  2018-01-19 17:13:12.875+00   
2272
75096540    2   Brighton    MI  Statesville NC  2018-01-19 17:09:38.037+00   
1600

Answer 1

您可以计算方差，然后将其加入到原始查询中。

SELECT DISTINCT booking.booking_id,
booking.price,shipment_log.pickup_city,
shipment_log.pickup_state,shipment_log.delivery_city,
shipment_log.delivery_state, tab.varPrice
FROM booking
INNER JOIN shipment_log
ON booking.booking_id = shipment_log.booking_id
AND booking.price = shipment_log.price
INNER JOIN   (select booking_id,
     VARIANCE(price) as varPrice
     from  shipment_log
     group by booking_id
     ) tab
ON tab.booking_id=booking.booking_id;