Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите написать два непростых запроса / 10 сообщений из 10, страница 1 из 1
13.06.2017, 23:03
    #39471195
CoiData
CoiData
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
Подскажите пожалуйста, как организовать два запроса:

Имеем исходную таблицу:
namepricequantitytimestampgoods11.07142017-06-13 18:29:01.15goods11.54112017-06-13 18:29:11.21goods12.0272017-06-13 18:29:32.303goods12.562017-06-13 18:29:49.62goods21.01122017-06-13 18:29:14.3goods21.0722017-06-13 18:29:32.44goods21.0592017-06-13 18:29:54.112

1. Выбрать строки по времени, от начала до конца прошлой минуты, и сгруппировать их в одну.

мысли пока такие.. но непонятно как группировать, в GROUP BY нельзя ведь дату?

Код: sql
1.
2.
(SELECT name, avg(price), sum(quantity) FROM TABLE1
WHERE (timestamp<=DATE_FORMAT((NOW()-INTERVAL 1 MINUTE), '%Y-%m-%d %H:%i')) and (timestamp>DATE_FORMAT ((NOW()-INTERVAL 2 MINUTE), '%Y-%m-%d %H:%i'))


Есть мысль сделать доп поле (UNIX_TIMESTAMP(DATE_FORMAT(timestamp,'%Y-%m-%d %H:%i'))), и группировать уже по нему.

в итоге нужно получить:
nameavg(price)sum(quantity)timestampgoods11.7825382017-06-13 18:29goods21.0433232017-06-13 18:29

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

Нужно получить из неё строки: (open-цена на начало периода, close - на конец)
nameopenclosetimestampgoods11.072.52017-06-13 18:29goods21.011.052017-06-13 18:29
это вообще ума не приложу как, кроме внешних скриптов на php. Должна же быть возможность на sql?

Подскажите как это сделать в принципе, и как сделать быстрее? т.к полей 15, а строк в обработке порядка 100к..
спасибо)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.06.2017, 23:19
    #39471198
CoiData
CoiData
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
Пока вариантов немного. Основные:

1.Доп поле UNIX_TIMESTAMP (описано выше) делать придется скорее всего так и так, для индекса и упрощения группировки.

2. С группировкой по UNIX_TIMESTAMP придется при сворачивании строк писать ещё флаг в отдельное поле (например GROUP) и писать туда - была группировка или нет (0,1).
При дальнейшей группировки увеличивать значение поля GROUP и отбирать с нулевым.

Но зато отпадает необходимость в плясках по поводу "от начала до конца прошлой минуты"), так более правильно имхо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 01:27
    #39471215
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
что нибудь типа:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SELECT
   name
  ,average_price
  ,sum_quantity
  ,t2.price  price_at_period_start
  ,t3.price  price_at_period_end
  ,t_minuta
FROM
(
SELECT 
   name
   ,DATE_FORMAT(time_col, '%Y-%m-%d %H:%i') t_minuta
   ,min(time_col) t_minimum
   ,max(time_col) t_maximum
   ,avg(price) average_price
   ,sum(quantity) sum_quantity
FROM TABLE1
group by DATE_FORMAT(time_col, '%Y-%m-%d %H:%i')
) t1

JOIN TABLE1 t2
ON t1.name = t2.name
AND  t1.t_minimum = t2.time_col

JOIN TABLE1 t3
ON t1.name = t3.name
AND  t1.t_maximum = t3.time_col
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 10:22
    #39471313
CoiData
CoiData
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
javajdbcчто нибудь типа:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SELECT
   name
  ,average_price
  ,sum_quantity
  ,t2.price  price_at_period_start
  ,t3.price  price_at_period_end
  ,t_minuta
FROM
(
SELECT 
   name
   ,DATE_FORMAT(time_col, '%Y-%m-%d %H:%i') t_minuta
   ,min(time_col) t_minimum
   ,max(time_col) t_maximum
   ,avg(price) average_price
   ,sum(quantity) sum_quantity
FROM TABLE1
group by DATE_FORMAT(time_col, '%Y-%m-%d %H:%i')
) t1

JOIN TABLE1 t2
ON t1.name = t2.name
AND  t1.t_minimum = t2.time_col

JOIN TABLE1 t3
ON t1.name = t3.name
AND  t1.t_maximum = t3.time_col



пишет: Col]umn 'name' in field list is ambiguous.

Если ставлю :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SELECT
   t1.name
  ,average_price
  ,sum_quantity
  ,t2.price  price_at_period_start
  ,t3.price  price_at_period_end
  ,t_minuta
FROM
(
SELECT 
   name
   ,DATE_FORMAT(time_col, '%Y-%m-%d %H:%i') t_minuta
   ,min(time_col) t_minimum
   ,max(time_col) t_maximum
   ,avg(price) average_price
   ,sum(quantity) sum_quantity
FROM TABLE1
group by DATE_FORMAT(time_col, '%Y-%m-%d %H:%i')
) t1

JOIN TABLE1 t2
ON t1.name = t2.name
AND  t1.t_minimum = t2.time_col

JOIN TABLE1 t3
ON t1.name = t3.name
AND  t1.t_maximum = t3.time_col


то результат нулевой. Что не так?)

таблица как в примере выше:
Код: sql
1.
2.
3.
4.
5.
6.
7.
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods1', 1.07, 14, '2017-06-13 18:29:01.15');
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods1', 1.54, 11, '2017-06-13 18:29:11.21');
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods1', 2.02, 7, '2017-06-13 18:29:32.303');
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods1', 2.50, 6, '2017-06-13 18:29:49.62');
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods2', 1.01, 12, '2017-06-13 18:29:14.3');
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods2', 1.07, 2, '2017-06-13 18:29:32.44');
INSERT INTO `TABLE1` (`name`, `price`, `quantity`, `time_col`) VALUES ('goods2', 1.05, 9, '2017-06-13 18:29:54.112');
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 12:16
    #39471431
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
CoiDataпишет: Column 'name' in field list is ambiguous.
Добавьте алиасы таблиц ВСЕМ полям:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SELECT
   t1.name
  ,t1.average_price
  ,t1.sum_quantity
  ,t2.price  price_at_period_start
  ,t3.price  price_at_period_end
  ,t1.t_minuta
FROM
(
SELECT 
   t0.name
   ,DATE_FORMAT(t0.time_col, '%Y-%m-%d %H:%i') t_minuta
   ,min(t0.time_col) t_minimum
   ,max(t0.time_col) t_maximum
   ,avg(t0.price) average_price
   ,sum(t0.quantity) sum_quantity
FROM TABLE1 t0
group by DATE_FORMAT(t0.time_col, '%Y-%m-%d %H:%i')
) t1

JOIN TABLE1 t2
ON t1.name = t2.name
AND  t1.t_minimum = t2.time_col

JOIN TABLE1 t3
ON t1.name = t3.name
AND  t1.t_maximum = t3.time_col
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 13:08
    #39471469
CoiData
CoiData
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
результат нулевой, пробовал как сам, так копировал ваш запрос как есть.
чего-то ещё не хватает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 13:32
    #39471486
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
CoiDataчего-то ещё не хватает?
Да, в подзапросе t1 нехватает группировки по t0.name
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 13:39
    #39471497
CoiData
CoiData
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
AkinaCoiDataчего-то ещё не хватает?
Да, в подзапросе t1 нехватает группировки по t0.name

Спасибо, дошло :) работает!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 14:53
    #39471554
CoiData
CoiData
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
Теперь другой не менее интересный вопрос, а как группировать не по минуте а по пять? по 15, по 30?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2017, 15:21
    #39471588
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать два непростых запроса
CoiDataкак группировать не по минуте а по пять?
Код: sql
1.
GROUP BY t0.name, UNIX_TIMESTAMP(t0.time_col) DIV 5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите написать два непростых запроса / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]