ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Помогите написать два непростых запроса
10 сообщений из 10, страница 1 из 1
Помогите написать два непростых запроса
    #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:03
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #39471198
CoiData
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CoiData
Гость
Пока вариантов немного. Основные:

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

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

Но зато отпадает необходимость в плясках по поводу "от начала до конца прошлой минуты"), так более правильно имхо.
...
Рейтинг: 0 / 0
13.06.2017, 23:19
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #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, 01:27
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #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, 10:22
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #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, 12:16
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #39471469
CoiData
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CoiData
Гость
результат нулевой, пробовал как сам, так копировал ваш запрос как есть.
чего-то ещё не хватает?
...
Рейтинг: 0 / 0
14.06.2017, 13:08
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #39471486
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
CoiDataчего-то ещё не хватает?
Да, в подзапросе t1 нехватает группировки по t0.name
...
Рейтинг: 0 / 0
14.06.2017, 13:32
| Ответить | Цитировать | Написать  
Помогите написать два непростых запроса
    #39471497
CoiData
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
CoiData
Гость
AkinaCoiDataчего-то ещё не хватает?
Да, в подзапросе t1 нехватает группировки по t0.name

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


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