напишите SQL-запрос, который извлекает все заказы с их ценой, pickup_location, местом доставки и отклонением цены.

avatar
Yash Mahendra
8 апреля 2018 в 00:31
195
2
0

Учитывая резервирование таблицы и таблицу shipping_log, зная, что обновления отгрузки могут происходить и регистрируются в таблице shipping_log, напишите запрос SQL, который извлекает все заказы с их ценой, pickup_location, местом доставки и ценовым отклонением. Описания таблиц:

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

shipment_log:

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
Источник
Yogs
8 апреля 2018 в 00:57
0

Не могли бы вы опубликовать пример кода вставки для обеих таблиц. Это сделает намного проще.

Yash Mahendra
8 апреля 2018 в 01:30
0

приложил образец

Yash Mahendra
8 апреля 2018 в 01:31
0

Я импортировал данные из файлов csv

Ответы (2)

avatar
jose_bacoy
8 апреля 2018 в 02:19
0

вы можете рассчитать отклонение, а затем присоединить его к исходному запросу.

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;
avatar
Yogs
8 апреля 2018 в 01:11
0

Если вы предоставили какие-либо образцы данных, было бы лучше проверить результат. Вы можете попробовать

SELECT DISTINCT booking.booking_id,
booking.price,
shipment_log.pickup_city,
shipment_log.pickup_state,
shipment_log.delivery_city,
shipment_log.delivery_state,
(SELECT  VARIANCE(shipment_log.price)           
FROM shipment_log join booking on 
booking.`booking_id`=shipment_log.`booking_id`
GROUP By shipment_log.booking_id) as 'variance'
FROM booking, shipment_log
WHERE booking.booking_id = shipment_log.booking_id
AND booking.price = shipment_log.price;

Это работает над сиквелом pro.enter image description here

Yash Mahendra
8 апреля 2018 в 01:29
0

Я выполнил ваш запрос, я получил следующую ошибку ОШИБКА: более одной строки возвращено подзапросом, используемым в качестве выражения Состояние SQL: 21000

Yogs
8 апреля 2018 в 03:28
0

Если вы опубликуете вышеуказанные данные в операторе вставки sql, будет легко проверить результат, а не просто экспортировать в файл csv. Преобразование их в оператор вставки занимает очень много времени. Другой вариант вы можете экспортировать в формате sql.