SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 10.02.2018 19:52:29

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Помогите правильно написать запрос SQL

Необходимо написать запрос результат которого ответит на вопрос: Сколько юзеров на каждом из продуктов в апреле месяце получили рефанд и удалили продукт, получили рефанд и оставили продукт. (С условием, что анинсталл был позже рефанда)?

2 таблицы, по другому не смогла загрузить, увы. изображение не грузится!

user_sales      Data_Type                user_events    Data_Type

user_id                INT                       date                DATE
product               STRING                  user_id                 INT
sum                        INT                  event              STRING
payment status       STRING                  product               STRING
date                        DATE

Отредактированно yulia (10.02.2018 20:12:39)

Неактивен

 

#2 10.02.2018 19:54:42

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

Здравствуйте. Не нужно дублировать одинаковые темы.
Приведите структуру таблиц и тестовые данные.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#3 10.02.2018 19:59:52

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

прошу прощения, мне выдавало ошибку при создание темы(

по какой то причине не могу добавить изображение таблицы

Неактивен

 

#4 10.02.2018 20:14:10

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

Не нужно добавлять изображение, пришлите
show create table
нужных таблиц
и
тестовые данные.
в идеале - создайте тестовый пример на sqlfiddle.com
и пришлите сюда вот как тут:

https://sqlinfo.ru/forum/viewtopic.php?pid=40086#p40086


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#5 10.02.2018 20:16:00

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

вариант запроса который я написала. только начала изучать SQL и знакома только с базовыми простыми запросами:

SELECT COUNT (user_sales.user_id)
FROM user_sales
LEFT JOIN user_events ON user_sales.user_id = user_events.user_id, user_sales.product = user_events.product
WHERE user_sales.date = '2016-04'
AND user_sales.payment_status = 'refund'
AND user_events.event = 'uninstall'
AND user_sales.date < user_events.date
GROUP BY user_sales.product


проверить нет возможности, закончился сертификат на офис, а MySQL нету на домашнем компе(((

Неактивен

 

#6 10.02.2018 20:23:16

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

Воспользуйтесь sqlfiddle.com,
создайте свои таблицы и тестовое наполнение похожее на
http://sqlfiddle.com/#!9/ae8b4/5

и сразу бросается в глаза
LEFT JOIN user_events ON user_sales.user_id = user_events.user_id, user_sales.product = user_events.product
я бы заменил на
LEFT JOIN user_events ON user_sales.user_id = user_events.user_id AND user_sales.product = user_events.product
, Вы же хотите делать объединение этих двух таблиц user_salse и user_events по этим двум критериям сразу?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#7 10.02.2018 20:55:48

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

да, по ним. и еще есть общее значение дата. по которому нужно ограничить период (апрель).

что-то у меня не особо получается на sqlfiddle.com sad

CREATE TABLE IF NOT EXISTS `user_sales` (
  `user_id` int(6) unsigned NOT NULL,
  `product` varchar(200) NOT NULL,
  `sum` int(10) unsigned NOT NULL,
  `payment status` varchar(200) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`user_id')
)
INSERT INTO `user_sales` (`user_id`, `product`, `sum`, `payment status`, `date`) VALUES
  ('
1', 'P','200','sale','2016-04-23'),
  ('
1', 'P','200','sale','2016-03-21'),
  ('
2', 'S','200','refund','2016-04-03'),
  ('
1', 'P','200','sale','2016-04-20');


выдает ошибки постоянно. с первого раза не могу разобраться(

Неактивен

 

#8 10.02.2018 21:03:59

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL


CREATE TABLE `user_sales` (
  `user_id` int,
  `product` varchar(200),
  `sum` int,
  `payment status` varchar(200),
  `date` date
  ) DEFAULT CHARSET=utf8;
 
CREATE TABLE `user_events`(
  `date` date,
  `user_id` int,
  `event` varchar(200),
  `product` varchar(200)
  ) DEFAULT CHARSET=utf8;

INSERT INTO `user_sales` (`user_id`, `product`, `sum`, `payment status`, `date`) VALUES
('1', 'P','200','sale','2016-04-23'),
('1', 'P','200','sale','2016-03-21'),
('2', 'S','200','refund','2016-04-03'),
('1', 'P','200','sale','2016-04-20'),
('2', 'P','200','refund','2016-04-01');

INSERT INTO `user_events` (`date`, `user_id`, `event`, `product') VALUES
('
2016-04-24','1','uninstall','P'),
('
2016-04-02','2','install','S'),
('
2016-04-02','1','uninstall','P'),
('
2016-04-20','1','uninstall','S');


наверное так...

Отредактированно yulia (10.02.2018 21:17:39)

Неактивен

 

#9 10.02.2018 21:30:06

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

А какие необходимые условия для того, чтобы товар попал в выборку? Перечислите пожалуйста.
То есть какие записи на конкретный продукт должны присутствовать в таблицах?
И сразу еще вопрос - структура таблиц подлежит изменениям или она задана "сверху"?
А то имхо тут лучше изменить структуру-то..
Это тестовое задание, курсовая или реальная рабочая задача?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#10 10.02.2018 21:53:51

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

Необходимо написать запрос результат которого ответит на вопрос: Сколько юзеров на каждом из продуктов в апреле месяце получили рефанд и удалили продукт, получили рефанд и оставили продукт. (С условием, что анинсталл был позже рефанда)?


это тестовое задание основано на реальной работе. структура таблиц была зада именно такая в самом тестовом задание, потому думаю, что не стоит ее менять. хотя да, не логичная...
результатом должна быть одна таблица. как мне кажется она должна иметь вид (product, refund+uninstall, refund+install) возможно еще date, но не обязательно. если запрос уже будет ограничен одним месяцем (апрелем). да и в задание не указано, что информация нужна по дням.


больше всего смущает условие в скобках. как его указать понятия не имею((

Отредактированно yulia (10.02.2018 21:59:48)

Неактивен

 

#11 10.02.2018 22:22:03

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

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


SELECT
  s.product
  ,count(s.`payment_status` = 'refund' AND e.`event` = 'uninstall' AND s.`date` < e.`date`) > 0
  ,count(s.`payment_status` = 'sale' AND e.`event` = 'install' AND s.`date` < e.`date`) > 0
FROM
  `user_sales` s
  JOIN `user_events` e ON s.`user_id` = e.`user_id` AND s.`product` = e.`product`
GROUP BY
  s.`product`


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#12 10.02.2018 22:41:17

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите правильно написать запрос SQL

а зачем условие count(...) > 0 ?
оно ведь не кол-во юзеров будет считать, а факт их наличия (0 или 1)

Неактивен

 

#13 10.02.2018 22:42:58

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

user_sales (user_id, product, payment status, date)

user_events (date, user_id, event, product)

одна строка - это один юзер. то есть нужно за месяц(апрель) выбрать все данные. а потом подсчитать количество юзеров у которых будет совпадать условия:
user_sales.payment_status - refund и user_events.event - uninstall;       
user_sales.payment_status - refund и user_events.event - install.

Отредактированно yulia (10.02.2018 22:45:24)

Неактивен

 

#14 10.02.2018 22:56:04

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

vasya написал:

а зачем условие count(...) > 0 ?
оно ведь не кол-во юзеров будет считать, а факт их наличия (0 или 1)

Да, это я погорячился, скорее так

SELECT
  s.product
  ,count(distinct s.`payment_status` = 'refund' AND e.`event` = 'uninstall' AND s.`date` < e.`date`)
  ,count(distinct s.`payment_status` = 'refund' AND e.`event` = 'install' AND s.`date` < e.`date`)
FROM
  `user_sales` s
  JOIN `user_events` e ON s.`user_id` = e.`user_id` AND s.`product` = e.`product`
GROUP BY
  s.`product`;


Ну и добавьте WHERE перед GROUP BY чтобы по дате отфильтровать.

distinct - чтобы  дубли не считались.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#15 10.02.2018 23:06:27

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

вопрос к дате.
как именно ее задать?

WHERE user_sales.date = '2016-04'
или
WHERE user_sales.date>= '2016-04-01' AND DATE <= '2016-04-30'
или
WHERE user_sales.date BETWEEN '2016-04-01' AND '2016-04-30'

Неактивен

 

#16 10.02.2018 23:28:55

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

'2016-04' так точно нельзя, остальные варианты можно.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#17 10.02.2018 23:33:24

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

deadka написал:

vasya написал:

а зачем условие count(...) > 0 ?
оно ведь не кол-во юзеров будет считать, а факт их наличия (0 или 1)

Да, это я погорячился, скорее так

SELECT
  s.product
  ,count(distinct s.`payment_status` = 'refund' AND e.`event` = 'uninstall' AND s.`date` < e.`date`)
  ,count(distinct s.`payment_status` = 'refund' AND e.`event` = 'install' AND s.`date` < e.`date`)
FROM
  `user_sales` s
  JOIN `user_events` e ON s.`user_id` = e.`user_id` AND s.`product` = e.`product`
GROUP BY
  s.`product`;


Ну и добавьте WHERE перед GROUP BY чтобы по дате отфильтровать.

distinct - чтобы  дубли не считались.

вопрос!!!

а зачем скобки на название колонок?

и почему просто JOIN, а не  LEFT JOIN?

дату нужно указывать в формате #07/04/1996# или '07/04/1996'?

Отредактированно yulia (10.02.2018 23:51:51)

Неактивен

 

#18 11.02.2018 00:19:16

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

смотрю на  запрос и меня смущает один момент
SELECT
  s.product

а не должно ли быть

SELECT
  s.user_id ?

мы ж по продукту разбиваем в
GROUP BY
  s.`product`

Неактивен

 

#19 11.02.2018 02:04:45

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите правильно написать запрос SQL

yulia написал:

смотрю на  запрос и меня смущает один момент
SELECT
  s.product

а не должно ли быть

SELECT
  s.user_id ?

ну, вы ведь сами написали, что

yulia написал:

результатом должна быть одна таблица. как мне кажется она должна иметь вид (product, refund+uninstall, refund+install)

Неактивен

 

#20 11.02.2018 09:03:46

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Помогите правильно написать запрос SQL

yulia написал:

а зачем скобки на название колонок?
и почему просто JOIN, а не  LEFT JOIN?
дату нужно указывать в формате #07/04/1996# или '07/04/1996'?

Что значит скобки на название колобок? символ `, которым обрамляю имена колонок это просто принятые правила оформления mysql-кода, давайте так считать.
JOIN - потому что нас вроде как интересуют только те продукты, у которых был uninstall.
Дата указывается в YYYY-MM-DD.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#21 11.02.2018 10:57:50

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

deadka написал:

yulia написал:

а зачем скобки на название колонок?
и почему просто JOIN, а не  LEFT JOIN?
дату нужно указывать в формате #07/04/1996# или '07/04/1996'?

Что значит скобки на название колобок? символ `, которым обрамляю имена колонок это просто принятые правила оформления mysql-кода, давайте так считать.
JOIN - потому что нас вроде как интересуют только те продукты, у которых был uninstall.
Дата указывается в YYYY-MM-DD.

На сколько я помню, в MySQL - который стационарный, при вводе названия колонки, ' - не появляется. Такой вариант написания я увидела в sqlfiddle.com, который вы посоветовали, кстати большое спасибо, это очень облегчит обучение в дальнейшем.

Не думайте, я не сомневаюсь в ваших знаниях, я просто хочу не тупо получить решение, а понять почему это должно быть именно так. И не будет ли это ошибкой, отдать решение с ' ?

JOIN- нас интересуют продукты у которых был только refund! В комбинации с uninstall и install. Но только с комбинацией uninstall нам нужно учитывать условие которое прописано в скобках (С условием, что анинсталл был позже рефанда). То есть условие с датой нужно толь в первом ?

 ,count(distinct user_sales.payment_status = 'refund' AND user_events.event = 'uninstall' AND user_sales.date < user_events.date)
  ,count(distinct user_sales.payment_status = 'refund' AND user_events.event = 'install')


DATE
изучать SQL я начала на этом сайте https://www.w3schools.com/  и у них есть вариант оформления даты не в скобках ('), а решеткой (#).


WHERE user_sales.date BETWEEN '2016-04-01' AND '2016-04-30'

такой вариант выборки месяца я пробовала как то раз. но выдало оно мне совершенно все даты, а не определенный месяц. Потому и уточняю у вас, возможно нужно было ставить решетку, вместо скобок?

Отредактированно yulia (11.02.2018 11:14:03)

Неактивен

 

#22 11.02.2018 11:13:26

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

vasya написал:

yulia написал:

смотрю на  запрос и меня смущает один момент
SELECT
  s.product

а не должно ли быть

SELECT
  s.user_id ?

ну, вы ведь сами написали, что

yulia написал:

результатом должна быть одна таблица. как мне кажется она должна иметь вид (product, refund+uninstall, refund+install)

подскажите, а таблица будет выглядеть вот так?

INSERT INTO `user_sales` (`product`, `refund+uninstall`, `refund+install`) VALUES
('P', '125','200'),
('S', '200','5'),
('F', '5','102'),
('G', '15','20'),
('K', '300','48');

таким образом я смогу переименовать колонки?

,count(distinct user_sales.payment_status = 'refund' AND user_events.event = 'uninstall' AND user_sales.date < user_events.date) AS `refund+uninstall`
  ,count(distinct user_sales.payment_status = 'refund' AND user_events.event = 'install') AS `refund+install`

Отредактированно yulia (11.02.2018 11:17:06)

Неактивен

 

#23 11.02.2018 16:35:19

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите правильно написать запрос SQL

не понял последний вопрос.
переформулируйте или лучше проиллюстрируйте на примере о чем идет речь

Неактивен

 

#24 11.02.2018 17:10:18

yulia
Участник
Зарегистрирован: 10.02.2018
Сообщений: 16

Re: Помогите правильно написать запрос SQL

SELECT
  user_sales.product
  ,count(distinct user_sales.payment_status = 'refund' AND user_events.event = 'uninstall' AND user_sales.date < user_events.date) AS refund_uninstall
  ,count(distinct user_sales.payment_status = 'refund' AND user_events.event = 'install') AS refund_install
FROM
user_sales
  JOIN user_events ON user_sales.user_id = user_events.user_id AND user_sales.product = user_events.product
WHERE user_sales.date BETWEEN '2016-04-01' AND '2016-04-30'
GROUP BY
  user_sales.product;

 


правильно ли я использовала фyнкцию AS?


в таблице которая получиться после запроса будет три колонки, правильно?
(product, refund_uninstall, refund_install)

Неактивен

 

#25 11.02.2018 17:17:45

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите правильно написать запрос SQL

yulia написал:

правильно ли я использовала фyнкцию AS?

в таблице которая получиться после запроса будет три колонки, правильно?
(product, refund_uninstall, refund_install)

Да.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson