powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Прошу помощи в создании запроса
13 сообщений из 13, страница 1 из 1
Прошу помощи в создании запроса
    #39266388
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Пользуюсь sqlitestudio, базу и таблицу создавал в консоли.
Сразу скажу, чтоб было понятно в дальнейшем, данные в таблице - секундные рыночные котировки

create table tab (dtr DATETIME, last NUMERIC, vol NUMERIC);

Пытаюсь создать такой запрос, чтоб не задействовать циклические операторы насколько это возможно. Увидел, что оператор strftime('%H', дата) возвращает час из даты, а %j - день в году. Эти два критерия необходимы мне для группировки значений

Вот моя таблица http://screencast.com/t/TFHYaqV34m (более 2 миллионов строк)
Задача - найти максимальные объемы (vol) по каждой цене (last) за конкретный час (00-23) Т.е. результат таблицы - 24 строки и 3 столбца (час 00-24), цена и максимальный объем).
Вот такой запрос у меня очень долго появлялся, но результат не тот, что надо, я и сам понимаю, но не знаю как правильно переделать запрос
SELECT strftime('%H',dtr) AS num_hour, last, MAX(vol) FROM 'tab' GROUP BY strftime('%H',dtr)
http://screencast.com/t/He8CTOCOr

Дело в том, что здесь выводится максимальная цена конкретного часа независимо от даты. И даже цена конкретного часа берется просто из какой то строки таблицы, а она должна предварительно ссумироваться по часу.
Например, цена 48,05 2016-06-01 течение времени с 12:00 до 12:59 появлялась в таблице 7 раз. Значит нужно сложить эти 7 объемов (vol) получается какая то сумма. Нужно просчитать все суммарные объемы по этому часу (12) ПО КАЖДОМУ дню в таблице и вывести в результат максимальный объем конкретной цены конкретного часа (самого большого из 365 дней).

Я взял %j, так как таблица не будет более 1 года, поэтому дни не будут пересекаться и каждый будет уникальным.

Ну вот, объяснил вроде подробно.

Спасибо, если поможете.
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266656
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Читай букварь про WHERE и ограничивай свой запрос одним днем.
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266664
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
White OwlЧитай букварь про WHERE и ограничивай свой запрос одним днем.

Я знаю что такое Where, но не могу сообразить, как его сюда прикрутить. Если ограничивать одним днем, то цикл нужно организовывать? Я уже что только не пробовал, но ничего не подходит. Иначе здесь бы не спрашивал.

Я понимаю, что здесь не благотворительная помощь, но хотя бы более конкретную наводку можно?

Спасибо.
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266666
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
В SQL нету циклических операторов. В принципе нет.
А тот кто действительно хочет помощи читает первый топик и думает над ним.
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266831
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
White OwlЧитай букварь про WHERE и ограничивай свой запрос одним днем.

Перечитал наверное уже все, что можно и про where и про sqlite в целом. Нигде не встретил даже зацепку на мой случай. Прочитал, что нельзя в подзапрос агрегатные функции вставлять, нельзя MAX(SUM(vol)). В итоге попал в тупик. Чтод ограничить запрос одним днем, нужно вставить WHERE примерно так
SELECT strftime('%H',dtr) AS num_hour, last, sum(vol) FROM 'tab' WHERE dtr LIKE '2016-06-01%' GROUP BY strftime('%H',dtr)
но мне же нужно все дни рассмотреть, ума не приложу как это реализовать без группировки и циклов)))
И так пробовал, но результат не корректен
SELECT strftime('%H',dtr) AS num_hour, last, sum(vol) FROM 'tab' GROUP BY strftime('%j',dtr), strftime('%H',dtr)

White OwlА тот кто действительно хочет помощи читает первый топик и думает над ним.
Не понял, что именно сказано под этой фразой. Если нужен структурированный вопрос, то я вроде дал его, а если про букварь, то поверьте, прочитано практически все, кроме темы работы с несколькими таблицами, т.к. у меня одна.

Попробую, конечно написать именно так как вы просите, надеюсь, что это приблизит меня к цели))

Код: sql
1.
2.
3.
4.
5.
create table tab (
dtr DATETIME, 
last NUMERIC, 
vol NUMERIC
);



Т.к. таблица специфическая, то экспортировал её в sql формат. Файл превышает разрешаемый размер, поэтому залил сюда https://cloud.mail.ru/public/HYGE/bzPijeUoJ
Вот скрин части базы http://screencast.com/t/ECH3nrebQEX

Задача следующая:
1. Найти промежуточные результаты SUM(vol) по каждой Last.
2. Результаты должны быть разбиты по каждому дню, а день на 24 часа. Т.е. 24 суммы в день.
3. Найти максимальную сумму из всех дней по каждому часу (00-24)
Окончательный результат - 24 строки (час - last - vol)

Мой созданный запрос приводит к результату максимальной Last по каждому часу без предварительного суммирования и без разбивки по дням
SELECT strftime('%H',dtr) AS num_hour, last, MAX(vol) FROM 'tab' GROUP BY strftime('%H',dtr)
http://screencast.com/t/He8CTOCOr
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266881
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Executioner73,

Что-то ваши условия врут:
1)
Executioner731. Найти промежуточные результаты SUM(vol) по каждой Last.
2. Результаты должны быть разбиты по каждому дню, а день на 24 часа. Т.е. 24 суммы в день.
Чтобы результаты получить в рамках дня и часа, делаем такую группировку...:
Код: sql
1.
2.
3.
select strftime('%Y-%m-%d %H', dtr) as num_day, strftime('%H', dtr) as num_hour, sum(vol)
from tab
group by strftime('%Y-%m-%d %H', dtr), strftime('%H', dtr)


Получим на каждый день, по 24 строки с суммой vol... Но, вы хотите ещё и last запихнуть в группировку, т.к. вам нужны суммы объёмов сгруппированных по цене (если правильно читаю ваш текст), итого у вас не будет 24 строки.

2)
Executioner733. Найти максимальную сумму из всех дней по каждому часу (00-24)
Окончательный результат - 24 строки (час - last - vol)
А здесь вообще не ясно, почему в итоге 24 строки? Т.е. нам нужно узнать максимальный объём на определенный час, при этом нас не интересует дата как таковая, нам нужно только время?

Луше на примере данных в "пальцах" (в Excel) покажите, как результат будет на ваших данных
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266888
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Спасибо огромное, что откликнулись

VSVLADExecutioner73,
Что-то ваши условия врут:
Получим на каждый день, по 24 строки с суммой vol... Но, вы хотите ещё и last запихнуть в группировку, т.к. вам нужны суммы объёмов сгруппированных по цене (если правильно читаю ваш текст), итого у вас не будет 24 строки.


Вы совершенно правильно заметили. Я не корректно указал условие. Но 24 строки в каждом дне точно. На пальцах:
1. В дне 24 часа
2. За каждый час по Last суммируем vol и оставляем Last с максимальным vol.
3. Получаем 24 Last с MAX(vol) в разрезе часа

2)
Executioner733. Найти максимальную сумму из всех дней по каждому часу (00-24)
Окончательный результат - 24 строки (час - last - vol)
А здесь вообще не ясно, почему в итоге 24 строки? Т.е. нам нужно узнать максимальный объём на определенный час, при этом нас не интересует дата как таковая, нам нужно только время?
[/quot]

Да, дата не интересует. Интересует статистика объемов именно по часам (время торговли фьючерсными контрактами). Поэтому и нужно суммирование по Last (цена), т.к. цена может несколько раз торговаться в течение заданного.
Дата здесь выступает в качестве промежуточного статистического результата. Мне нужны максимальные объемы (vol) в определенный час, т.е. в какой час проявляется активность торгов.
В данной таблице данные за месяц, а так будет обрабатываться 3-6 месяцев (от 2 до 5 млн.строк)
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266893
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Код: sql
1.
2.
3.
select strftime('%Y-%m-%d %H', dtr) as num_day, strftime('%H', dtr) as num_hour, sum(vol)
from tab
group by strftime('%Y-%m-%d %H', dtr), strftime('%H', dtr)



К сожалению, результат по дню-часу оказался не корректен. Я добавил в Select last
Код: sql
1.
2.
3.
select strftime('%Y-%m-%d %H', dtr) as num_day, strftime('%H', dtr) as num_hour, [color=red]last[/color], sum(vol)
from tab
group by strftime('%Y-%m-%d %H', dtr), strftime('%H', dtr)


и проверил правильность одной из цен на калькуляторе))). Мой результат в 3 раза меньше вышел. Должно быть 194 http://screencast.com/t/weTbUNPIKLYt
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266894
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Executioner73,

Набросок, нужно отлаживать

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
with day_sumvol as (
     select strftime('%Y-%m-%d %H', dtr) as num_day, strftime('%H', dtr) as num_hour, last, sum(vol) sumvol
     from tab
     group by strftime('%Y-%m-%d %H', dtr), strftime('%H', dtr), last
),
day_maxvol as (
     select num_day, num_hour, max(sumvol) maxvol
     from day_sumvol
     group by num_day, num_hour
)
select num_hour, max(sumvol) total
from (
    select day_sumvol.num_day, day_sumvol.num_hour, min(day_sumvol.last) last, day_sumvol.sumvol
    from day_maxvol
    inner join day_sumvol on day_sumvol.num_day = day_maxvol.num_day
                         and day_sumvol.sumvol = day_maxvol.maxvol                     
    group by day_sumvol.num_day, day_sumvol.num_hour, day_sumvol.sumvol
)
group by num_hour
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266897
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
VSVLADExecutioner73,

Набросок, нужно отлаживать

Знаете, а круто! Спасибо огромное! Правда надо все скурпулезно проверить, но на глаз пока все правильно.
Сколько я вам должен?

И еще вопрос можно?
Как неделю и месяц сделать теперь мне понятно.
А чтоб тоже самое сделать, но в разрезе 15 минут и 30 минут (вместо часа все тоже самое), то как подправить , ведь минуты %M не выдает четвертями.
Код: sql
1.
strftime('%H', dtr)



и вот здесь обязательно нужно было строку до %Н выводить? Разве бы не хватило ('%Y-%m-%d', dtr),
Код: sql
1.
group by strftime('%Y-%m-%d %H', dtr),
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266928
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Executioner73Сколько я вам должен?
Это по желанию, если вдруг возникло, напишите на почту =) (см профиль)

Executioner73А чтоб тоже самое сделать, но в разрезе 15 минут и 30 минут
Использовать конструкцию, типо такой в Select и Group by:
Код: sql
1.
2.
3.
4.
5.
case when group by strftime('%m', dtr) >=  0 and strftime('%m', dtr) < 15 then 1
     when group by strftime('%m', dtr) >= 15 and strftime('%m', dtr) < 30 then 2
     when group by strftime('%m', dtr) >= 30 and strftime('%m', dtr) < 45 then 3
     when group by strftime('%m', dtr) >= 45 and strftime('%m', dtr) < 60 then 4
end



Executioner73Разве бы не хватило ('%Y-%m-%d', dtr),
Да хватило бы, т.к. потом я всё равно группирую ещё по часам. Но если захотите сделать именно так, тогда нужно будет ещё добавить условие в джойн, сюда:
Код: sql
1.
2.
3.
inner join day_sumvol on day_sumvol.num_day = day_maxvol.num_day
                     and day_sumvol.num_hour = day_maxvol.num_hour
                     and day_sumvol.sumvol = day_maxvol.maxvol



Код я не комментировал, но если в SQL имеются понимание, там достаточно легко читается всё:
1) Группируем по дням, часам, цене, сумма объёмов
2) Группируем всё что в первой части по дням, часам, объёмам
3) Джойним ко второй выборке, первую выборку, да так, чтобы выбрать на каждую дату, час, максимальный объём и цену, для которой есть такой объём. (Цену берём MIN() т.к. в данных заметил, что существует две разные цены с одинаковым максимальным объёмом)
4) Группируем это всё по часам
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266933
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
VSVLADИспользовать конструкцию, типо такой в Select и Group by
чуть накосячил в запросе, но идея понятна
...
Рейтинг: 0 / 0
Прошу помощи в создании запроса
    #39266943
Executioner73
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
VSVLADExecutioner73Сколько я вам должен?
Это по желанию, если вдруг возникло, напишите на почту =) (см профиль)


Написал...
...
Рейтинг: 0 / 0
13 сообщений из 13, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Прошу помощи в создании запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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