Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / SQLite Новый топик    Ответить
 Использование в цикле несколько таблиц подвешивает SQL  [new]
QFireball
Member

Откуда:
Сообщений: 6
Здравствуйте, есть задача написать запрос, делающий запросы в цикле к базе данных по финансовой программе financisto (Android).
Вынул базу данных - хочу получить таблицу: в первой колонке год-месяц YYYYMM, во второй - общие траты по кошельку. В исходной базе данных есть таблица transactions со всеми тратами по всем кошелькам, откуда можно посчитать все.

За определенный указанный период (например за сентябрь 2018) я это сделать могу запросом:
SELECT SUM(ROUND(transactions.from_amount/100.0, 2)) AS money 
FROM transactions
 WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0 AND 
   (strftime('%Y%m%d', transactions.datetime/1000, 'unixepoch', 'localtime') >='20180901') AND
   (strftime('%Y%m%d', transactions.datetime/1000, 'unixepoch', 'localtime') <='20180919')


Могу отдельно создать таблицу такого типа (1я колонка - год-месяц, 2я-просто пробное число):
WITH RECURSIVE
    for(DATATM, MONEYSPEND) AS (VALUES('201800', 0) UNION ALL SELECT (DATATM + 1), (MONEYSPEND) FROM for 	     
		 WHERE
			 DATETM < '201809')


Но с циклами не удалось разобраться (читал в вики, хабр и другие источники) и совмещая эти два запроса SQL зависает и начинает потреблять 100% CPU. Вот что получилось:
[/SRC]

При добавлении в запрос таблицы transactions SQL зависает:
[SRC sql]
WITH RECURSIVE
    for(DATATM, MONEYSPEND) AS (VALUES('201800', 0) UNION ALL SELECT (DATATM + 1), (MONEYSPEND) FROM for, transactions	     
		 WHERE
			 DATETM < '201809')

вместо MONEYSPEND хочу вставить самый первый запрос.

Помогите разобраться с циклом в sqlite и использовании второй таблицы transactions. Возможно это можно сделать еще как-то по-другому.
20 сен 18, 02:59    [21680216]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
Dima T
Member

Откуда:
Сообщений: 12871
QFireball
Но с циклами не удалось разобраться (читал в вики, хабр и другие источники) и совмещая эти два запроса SQL зависает и начинает потреблять 100% CPU.

Не зависает, а выполняется в соответствии с запрошенным.

QFireball
... FROM for, transactions	     
		 WHERE
			 DATETM < '201809')

В данном случае ты запросил декартово произведение, т.е. результат будет количество записей for.DATETM < '201809' умножить на количество записей transactions. Думаю что это очень много.

Таблицы for и transactions надо связать с помощью join
20 сен 18, 07:00    [21680247]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
QFireball
Member

Откуда:
Сообщений: 6
Dima T,

попробовал вот так с помощью подзапроса, но все равно зависает:

WITH RECURSIVE
    for(DATATM, MONEYSPEND) 
AS (
				VALUES('201800', 0) UNION ALL 
					SELECT DATATM, 
						(
							SELECT SUM(ROUND(transactions.from_amount/100.0, 2)) AS MONEYSPEND 
							FROM transactions
							WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0 AND 
							(strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') >= DATATM ) AND
							(strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') <  DATATM + 1)
						)
					FROM for
					WHERE DATATM < '201809'
		   )
		   
SELECT DATATM, MONEYSPEND FROM for;


Dima T
Таблицы for и transactions надо связать с помощью join

Не понял, с точки зрения логики, как это реализовать и что это даст.
25 сен 18, 04:38    [21684870]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
QFireball
Member

Откуда:
Сообщений: 6
ура сделал почти все! но все равно буду благодарен исправлениям и комментариям.
Мешает первая строка в таблице, оставшаяся от инициализации

(201800, 0)


Можно ли ее как-то грамотно проинициализировать? Без нее тогда не получится цикл. Писать сразу 201801 и делать подзапрос в VALUES и потом такой же подзапрос делать в самом "цикле" в AS не хочется.

Сорри за рассыпающийся код. Как его правильно сюда вставлять из notepad++? Табы я так понимаю здесь на форуме не приветствуются?

WITH RECURSIVE
    for(DATATM, MONEYSPEND) 
       AS (
              VALUES(201800, 0) UNION ALL 
              SELECT DATATM + 1, 
              (
                 SELECT SUM(ROUND(transactions.from_amount/100.0, 2)) AS MONEYSPEND 
                 FROM transactions
                 WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0 AND 
                           (strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') >= cast( (DATATM + 1) as text) ) AND
                           (strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') <  cast( (DATATM + 2) as text) )
              )
       FROM for
       WHERE DATATM < 201809
	   )
		   
SELECT DATATM, MONEYSPEND FROM for;


Еще бы переменные реализовать, чтобы константы вынести наверх, как в нормальных языках программирования типа Си, но я как понял в sqlite с этим проблема.
И все таки как можно было бы с JOIN сделать?
25 сен 18, 05:13    [21684875]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
QFireball
Member

Откуда:
Сообщений: 6
и еще: а как ходить по дням?
по месяцам вроде просто 201801 + 1 = 201802. А вот по дням с 20180131 + 1 не равно 20180201
25 сен 18, 05:15    [21684876]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
White Owl
Member

Откуда:
Сообщений: 12316
Читал, читал... ниче не понял. Что ты хочешь получить в итоге???

Вообще, есть правило: если начал задумываться о цикле - значит тебе надо забыть про SQL.
Цикл делается на клиенте, и в нем посылается запрос, результат обрабатывается клиентом, и повторять до удовлетворения.
25 сен 18, 15:06    [21685532]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
Dima T
Member

Откуда:
Сообщений: 12871
White Owl
Читал, читал... ниче не понял. Что ты хочешь получить в итоге???

Вообще, есть правило: если начал задумываться о цикле - значит тебе надо забыть про SQL.
Цикл делается на клиенте, и в нем посылается запрос, результат обрабатывается клиентом, и повторять до удовлетворения.

Подозреваю он с датами запутался и изобрел этот чудо-запрос для группировки по дням/месяцам, вместо обычного GROUP BY date или GROUP BY year(date), month(date), которые есть в других СУБД.

PS Я в sqlite с датами не пробовал работать, тут с ними все не просто, не подскажу как правильно GROUP BY написать

PPS QFireball, с месяцами тоже неверно: 201812 + 1 = 201813
25 сен 18, 15:38    [21685576]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
Dima T
Member

Откуда:
Сообщений: 12871
Если я правильно догадался, то попробуй так
SELECT strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') as DATATM
             , SUM(ROUND(transactions.from_amount/100.0, 2)) as MONEYSPEND
    FROM transactions
    WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0
               AND strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') BETWEEN '201801' AND '201808'
    GROUP BY strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime')

Не запускал. Может не заработает. Скомбинировал твой запрос и мои догадки.

PS Если правильно, то можно попробовать позаменять strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') на DATATM
25 сен 18, 16:40    [21685696]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
QFireball
Member

Откуда:
Сообщений: 6
White Owl
Читал, читал... ниче не понял. Что ты хочешь получить в итоге???

Вообще, есть правило: если начал задумываться о цикле - значит тебе надо забыть про SQL.
Цикл делается на клиенте, и в нем посылается запрос, результат обрабатывается клиентом, и повторять до удовлетворения.


Есть табличка transactions, содержащая много колонок с тратами по всем кошелькам из программы financisto для Android. Из них интересуют: сумма, время в стиле timestamp unix epoch time, название кошелька, название покупки, сумма. Для каждой транзакции своя запись.

Типа такой таблицы:
id кошелек сумма время_в_формате_unix_epoch название_покупки и много-много других колонок
1 10 10000 1450789572605 продукты
2 10 2000 1452414977321 Интернет
3 10 350 1452674435041 Такси

Хочу извлечь в одну табличку по периодам, сколько в сумме было потрачено с заданного кошелька. То есть хочу получить, табличку типа такой:

Дата Сумма
201801 10000
201802 20000
201803 15000

Я в SQL новичек. Раньше прогал под С++, PHP, bash. И разбил задачу на части. Казалось бы логично все. Сначала беру запросом вычисляю, сколько потрачено за январь 2018 записываю в таблицу, потом снова запрос - за февраль 2018. В планах сделать запрос, сколько потрачено за каждый день (за год - с 1 января до 31 декабря), к примеру так:

20180101 3500
20180102 750
20180103 5400

Как это без цикла сделать? не понимаю.

Dima T
Подозреваю он с датами запутался и изобрел этот чудо-запрос для группировки по дням/месяцам, вместо обычного GROUP BY date или GROUP BY year(date), month(date), которые есть в других СУБД.


Не понял, как можно использовать GROUP BY date и GROUP BY year(date), month(date). Я пробегаюсь по всей базе и вытаскиваю только те транзакции, которые были за январь 2018 например, потом суммирую и результат кладу во второй столбец, а потом в цикле до конца периода - до августа 2018. Вроде бы логично. Буду рад, если опишите суть другого более правильного метода.

С месяцами попроще: я просто задаю запрос: дай мне табличку с января 2017 по декабрь 2017 с 12ю строчками, посчитанную на базе transactions. Я ее не извлекаю а строю. Если бы были большие двумерные массивы на С бы не составило труда это все написать. но тут база данных...

Dima T
Не запускал. Может не заработает. Скомбинировал твой запрос и мои догадки.

PS Если правильно, то можно попробовать позаменять strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') на DATATM

Запрос работает как надо! :) Спасибо большое! Осталось разобраться как и что происходит. Не совсем понял, где менять strftime. Разбираюсь.
26 сен 18, 01:57    [21686111]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
Dima T
Member

Откуда:
Сообщений: 12871
QFireball
Запрос работает как надо! :) Спасибо большое! Осталось разобраться как и что происходит.

Почитай что-нибудь по языку SQL. Нет смысла сюда основы перепечатывать. SQL отличается от алгоритмических языков.
QFireball
Не совсем понял, где менять strftime. Разбираюсь.

Так
SELECT strftime('%Y%m', transactions.datetime/1000, 'unixepoch', 'localtime') as DATATM
             , SUM(ROUND(transactions.from_amount/100.0, 2)) as MONEYSPEND
    FROM transactions
    WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0
               AND DATATM BETWEEN '201801' AND '201808'
    GROUP BY DATATM

Но возможно в таком виде не заработает.


Еще можно ускорить работу индексом по transactions.datetime если преобразовать к виду
    WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0
               AND transactions.datetime BETWEEN ... AND ...
26 сен 18, 07:12    [21686177]     Ответить | Цитировать Сообщить модератору
 Re: Использование в цикле несколько таблиц подвешивает SQL  [new]
QFireball
Member

Откуда:
Сообщений: 6
Dima T,
Спасибо большое, все работает как хотел! Проблема решена
15 окт 18, 23:33    [21704615]     Ответить | Цитировать Сообщить модератору
Все форумы / SQLite Ответить