Простые задачи по SQL.

[LazyFool1]

Пока выложу только первую задачу.

You have 2 tables:

Users (
id primary key (int),
name (text)
)

Orders (
id primary key (int),
user_id foreign key to users (int),
created_at (timestamp)
)

Users can have zero or more orders. Most users have just a few orders, some have dozens or even hundreds. Many have zero orders.

1) Write an SQL query to return all users that have at least one order. Every user should come with 3 most recent orders (or fewer if this user has fewer than 3).

Ответ(?):

Таблицы

Users
id primary key (int) || name (text)


Orders
id primary key (int) || user_id foreign key to users (int) || created_at (timestamp)

Команды

Используется синтаксис Oracle 11 SQL

 
DECLARE min_user_id int;
 
SELECT min_user_id=MIN(id) FROM Users;
 
DECLARE max_user_id int;
 
SELECT max_user_id=MAX(id) FROM Users;
 
DECLARE i int;
 
SET i=min_user_id;
 
   WHILE i<=max_user_id DO
 
     SELECT Users.id, name, Orders.id, created_at
 
        FROM Users, Orders 
 
        WHERE Users.id=Orders.user_id AND Orders.user_id=i 
 
        ORDER BY created_at DESC 
 
        LIMIT 3;
 
     SET i=i+1;
 
   END WHILE;
 

Это правильно?

[rockclimber]

Нет конечно. Надо было один SQL запрос написать, а у вас что?

[LazyFool1]

rockclimber, буду вам чрезвычайно признателен, если подскажете: как это сделать одним запросом.

[Pphantom]

Тема перемещена из форума «Программирование» в форум «Карантин»
по следующим причинам:

- неправильно оформлен код (и про подсветку синтаксиса не забудьте).

Исправьте все Ваши ошибки и сообщите об этом в теме Сообщение в карантине исправлено.
Настоятельно рекомендуется ознакомиться с темами Что такое карантин и что нужно делать, чтобы там оказаться и Правила научного форума.

[Lia]

Тема перемещена из форума «Карантин» в форум «Программирование»

[iifat]

А какой SQL имеется в виду? Оракловый как у вас в сообщении?

[rockclimber]

iifat
Простите великодушно, но этот набор букв к ораклу не имеет никакого отношения

LazyFool1 в сообщении #1308677 писал(а):

rockclimber, буду вам чрезвычайно признателен, если подскажете: как это сделать одним запросом.

На этот вопрос сложно ответить, не дав практически окончательного ответа. Две таблицы соединить, далее, в зависимости от вашего диалекта SQL, отсортировать записи нужным способом и выбрать те, что подходят под условия задачи...

[iifat]

rockclimber в сообщении #1308806 писал(а):

этот набор букв к ораклу не имеет никакого отношения

И правда что. Я, грешным делом, пока неизвестен диалект, и смотреть не стал — на каноническом-то SQL задача не то крайне муторная, не то совершенно нерешаемая...

rockclimber в сообщении #1308806 писал(а):

Две таблицы соединить, далее, ... отсортировать ... и выбрать

Не, там, имхо, посложнее будет из-за

LazyFool1 в сообщении #1308675 писал(а):

Every user should come with 3 most recent orders

Это, как понимаю, означает, что три последних ордера (пардон за перевод) надо вытянуть в одну строку... Впрочем, это, пожалуй, второй вопрос к ТС — чего конкретно ему нужно.

[rockclimber]

iifat в сообщении #1308829 писал(а):

Это, как понимаю, означает, что три последних ордера (пардон за перевод) надо вытянуть в одну строку...

По умолчанию в SQL подразумевается, что нет - в три строки. Разворачиваение в строку ("пивот") - довольно нетривиальная задача (по крайней мере, на начальном этапе обучения), и обычно оговаривается отдельно. В стандарте SQL для нее даже не предусмотрено никакой специальной конструкции, а те СУБД, которые это умеют, решают задачу каждая по-своему.

[LazyFool1]

Я чуть-чуть уточнил код, в том смысле, что добавил знак @ к названиям всех переменных, изменил синтаксис цикла WHILE, а так же написал подробные комментарии к командам.

Используется синтаксис SQL

DECLARE @min_user_id INT;
/*Определяем минимальный id пользователя*/ 
SELECT @min_user_id=MIN(id) FROM Users;
DECLARE @max_user_id INT;
/*Определяем максимальный id пользователя*/
SELECT @max_user_id=MAX(id) FROM Users;
DECLARE @i INT;
/*Присваиваем вспомогательной переменной @i минимальное id пользователя*/
SET @i=@min_user_id;
/*Запускаем цикл выборки по всем пользователям (по их id в переменной @i), в котором ищем их три последних заказа.*/
   WHILE @i<=@max_user_id 
     BEGIN
/*Для лучшего отображения результатов используем CONCAT.*/
      SELECT CONCAT(‘The USER ’, Users.id, ‘ ‘, name, ‘ made an ORDER ’, 
                 Orders.id, ‘ at ’, created_at)  
        FROM Users, Orders 
        WHERE Users.id=Orders.user_id AND Orders.user_id=@i 
/*Сортируем по дате заказа в порядке убывания.*/
        ORDER BY created_at DESC 
/*Оставляем только три первых.*/
        LIMIT 3;
      SET @i=@i+1;
     END;

 

-- 30.04.2018, 23:39 --

Для той же схемы (для тех же таблиц)

Users
id primary key (int) || name (text)


Orders
id primary key (int) || user_id foreign key to users (int) || created_at (timestamp)

нужно решить ещё одну задачу:

2) Calculate the global average time there is between orders of the same user. For example, a possible answer could be "The average user with at least 1 order takes around 35 hours to make a new order".

Используется синтаксис SQL

DECLARE @number_of_steps INT;
/* С помощью переменной @number_of_steps (количество «шагов») мы будем подсчитывать количество промежутков между заказами у одних и тех пользователей. Иными словами, 1 шаг это 1 промежуток между двумя последовательными заказами одного и того же пользователя.*/
SET @number_of_steps=0;
DECLARE @total_length INT;
/*С помощью переменной @total_length (общая «длина») мы будем подсчитывать длину всех «шагов» в часах. Иными словами, будем суммировать «расстояния» по времени между двумя последовательными заказами одного и того же пользователя.*/
SET @total_lengt=0;
DECLARE @min_user_id INT;
/*Определяем минимальный id пользователя*/
SELECT @min_user_id=MIN(id) FROM Users;
DECLARE @max_user_id INT;
/*Определяем максимальный id пользователя*/
SELECT @max_user_id=MAX(id) FROM Users;
DECLARE @i INT;
/*Присваиваем вспомогательной переменной @i минимальное id пользователя*/
SET @i=@min_user_id;
/*Запускаем цикл выборки по всем пользователям (по их id в переменной @i), в котором суммируем количество шагов и их длину у каждого пользователя. Переменные @number_of_steps  и @total_length соответственно.*/
   WHILE @i<=@max_user_id 
    BEGIN
     SELECT 
/*@number_of_steps= @number_of_steps+max{COUNT(Orders.id)-1, 0}*/
       @number_of_steps= @number_of_steps+(ABS(COUNT(Orders.id)-1)+   
              COUNT(Orders.id)-1)/2,
/*Функция DATEDIFF даёт «расстояние» в часах между последним и первым заказом данного пользователя (с id=@i)*/
       @total_length= @total_length+DATEDIFF(hh, MIN(created_at),   
              MAX(created_at))
      FROM Users, Orders 
      WHERE Users.id=Orders.user_id AND Orders.user_id=@i; 
     SET @i=@i+1;
    END;
IF @number_of_steps>0
  SELECT ‘The average USER WITH at least 1 ORDER takes around ’ ||   
                 ROUND(@total_length/@number_of_steps) || ‘ hours TO make a 
                 NEW ORDER.’;
 ELSE
  SELECT ‘The average USER WITH at least 1 ORDER takes around ’ ||   
                 @total_length || ‘ hours TO make a 
                 NEW ORDER.’;

 

Хотелось бы услышать ваше мнение.

[rockclimber]

А, так у вас MS SQL...
Я наслышан о том, что у MS все не как улюдей, но неужели там нельзя писать что-то вроде

Используется синтаксис SQL

SELECT *
FROM table1 JOIN table2 ON ...
WHERE ...

и так далее? Это и будет SQL. А то, что вы написали, - это, как я понимаю, TSQL.

[iifat]

rockclimber в сообщении #1308922 писал(а):

неужели там нельзя писать что-то вроде

MS SQL не знаю, а в MS Access можно — думаю, и в MS SQL обязано присутствовать.
TSQL не знаю тем паче, но как-то мне сомнительно, чтоб все эти selectы в цикле без каких либо пометок передавались результатом функции...

[gevaraweb]

rockclimber в сообщении #1308922 писал(а):

Я наслышан о том, что у MS все не как улюдей, но неужели там нельзя писать что-то вроде...

Может, и можно, но голову ломать дольше. Я вот не пробовал такие запросы писать (на крайняк js выручал, досчитывал в браузере что нужно ). Какой там ответ, интересно? можете привести, раз ТС он не нужен все равно?

[rockclimber]

iifat в сообщении #1308975 писал(а):

MS SQL не знаю, а в MS Access можно — думаю, и в MS SQL обязано присутствовать.

Это был риторический вопрос. У всех конечно можно, все СУБД начинали с голого SQL, а потом добавляли процедурные расширения. Просто я ни разу не видел, чтобы кто-то в процессе обучения процедурное расширение освоил, а SQL - нет. Даже не знаю, как тут объяснить.

gevaraweb в сообщении #1309009 писал(а):

Какой там ответ, интересно? можете привести, раз ТС он не нужен все равно?

Пока нет оснований считать, что ТСу ответ не нужен - не так много времени прошло. А полные ответы давать нельзя. В конце концов, если вам тоже нужен SQL - тоже изучайте и делайте попытки решения

(Оффтоп)

Я, кажется, такое сообщение уже писал, а оно пропало. Или я отправить забыл?

[LazyFool1]

Простите, ещё одна задача.
3) Provide a list of the order types, ordered by the absolute difference between the average price and the median price. I.e the last order type X has the biggest difference when comparing the average price of X against the median price of X.

Suppose we have schema:
Order_Type (
id primary key (int),
name (varchar)
)

Order (
id primary key (int),
order_type_id foreign key to order_type (int),
price (double)
)

We have lots of orders for each order type. Suppose we have around a dozen order types and around 100k orders.

Order_Type
id primary key (int) || name (varchar)


Order
id primary key (int) || order_type_id foreign key to order_type (int) || price (double)

Ответ(?):

Используется синтаксис SQL

                

/*Создаём вспомогательную таблицу Order_type_with_abs_diff_AP_MP для хранения абсолютной разности между средней ценой (СЦ) и медианной ценой (МЦ) заказов по каждому типу.*/
CREATE TABLE Order_type_with_abs_diff_AP_MP (
id INT NOT NULL PRIMARY KEY, 
/*Для следующей колонки abs_diff определяем возможность значения NULL, если, к примеру, заказов данного типа ещё не было.*/
abs_diff DOUBLE NULL);
/*Определяем минимальный id типа заказа*/
DECLARE @min_oreder_type_id INT;
SELECT @min_oreder_type_id=MIN(id) FROM Order_Type;
/*Определяем максимальный id типа заказа*/
DECLARE @max_oreder_type_id INT;
SELECT @max_oreder_type_id=MAX(id) FROM Order_Type;
/*Определяем переменную для хранения вычисленной абсолютной разницы между СЦ и МЦ для конкретного типа заказа.*/
DECLARE @abs_diff_v DOUBLE;
DECLARE @i INT;
/*Присваиваем вспомогательной переменной @i минимальное id типа заказа.*/
SET @i=@min_oreder_type_id;
/*Запускаем цикл выборки по всем типам заказа (по их id в переменной @i), в котором вычисляем абсолютную разность между СЦ и МЦ по каждому типу заказа и добавляем эту разность в созданную таблицу Order_type_with_abs_diff_AP_MP.*/
   WHILE @i<=@max_oreder_type_id
    BEGIN
     SELECT 
        @abs_diff_v=ABS(AVG(price)-(MAX(price)+MIN(price))/2)
        FROM ORDER
        WHERE ORDER.order_type_id =@i;
     INSERT INTO Order_type_with_abs_diff_AP_MP (id, abs_diff) 
                                         VALUES (@i,  @abs_diff_v);
     SET @i=@i+1;
    END;
/*Отображаем на экран id типа заказа, название типа и его абсолютную разность, выбираем только те типы, для которых абсолютная разность не NULL, т.е. заказы такого типа были, и сортируем их в порядке возрастания абсолютной разности.*/
SELECT Order_Type.id, name, abs_diff
  FROM Order_Type, Order_type_with_abs_diff_AP_MP 
  WHERE Order_Type.id=Order_type_with_abs_diff_AP_MP.id 
              AND Order_type_with_abs_diff_AP_MP.abs_diff IS NOT NULL
  ORDER BY abs_diff;
/*Можно ли уже в следующей команде удалить вспомогательную таблицу? 
DROP TABLE Order_type_with_abs_diff_AP_MP;
*/
 

Ещё пара вопросов. Команда

Используется синтаксис SQL

     SELECT CONCAT(‘The USER ’, CONVERT(VARCHAR, Users.id), ‘ ‘, name,
                   ‘ made an ORDER ’, CONVERT(VARCHAR, Orders.id), ‘ at ’, 
                   CONVERT(VARCHAR, created_at))  
 

для пустой выборки отобразит что-то на экран?
Можно ли так писать:

Используется синтаксис SQL

SELECT Order_Type.id, name, abs_diff
  FROM Order_Type, Order_type_with_abs_diff_AP_MP 
  WHERE Order_Type.id=Order_type_with_abs_diff_AP_MP.id 
 

если в двух разных таблицах первые столбцы названы одинаково?
Значения переменных SQL, наверняка(?), распознает в команде:

Используется синтаксис SQL

INSERT INTO Order_type_with_abs_diff_AP_MP (id, abs_diff) 
                                    VALUES (@i,  @abs_diff_v);
 

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