ReSQL.ru
     
powered by simpleCommunicator - 2.0.36     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Использование в цикле несколько таблиц подвешивает SQL
11 сообщений из 11, страница 1 из 1
Использование в цикле несколько таблиц подвешивает SQL
    #39705337
QFireball
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
QFireball
Гость
Здравствуйте, есть задача написать запрос, делающий запросы в цикле к базе данных по финансовой программе financisto (Android).
Вынул базу данных - хочу получить таблицу: в первой колонке год-месяц YYYYMM, во второй - общие траты по кошельку. В исходной базе данных есть таблица transactions со всеми тратами по всем кошелькам, откуда можно посчитать все.

За определенный указанный период (например за сентябрь 2018) я это сделать могу запросом:
Код: sql
1.
2.
3.
4.
5.
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я-просто пробное число):
Код: sql
1.
2.
3.
4.
WITH RECURSIVE
    for(DATATM, MONEYSPEND) AS (VALUES('201800', 0) UNION ALL SELECT (DATATM + 1), (MONEYSPEND) FROM for 	     
		 WHERE
			 DATETM < '201809')



Но с циклами не удалось разобраться (читал в вики, хабр и другие источники) и совмещая эти два запроса SQL зависает и начинает потреблять 100% CPU. Вот что получилось:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
[/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. Возможно это можно сделать еще как-то по-другому.
...
Рейтинг: 0 / 0
20.09.2018, 02:59
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39705362
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
QFireballНо с циклами не удалось разобраться (читал в вики, хабр и другие источники) и совмещая эти два запроса SQL зависает и начинает потреблять 100% CPU.
Не зависает, а выполняется в соответствии с запрошенным.

QFireball
Код: sql
1.
2.
3.
... FROM for, transactions	     
		 WHERE
			 DATETM < '201809')


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

Таблицы for и transactions надо связать с помощью join
...
Рейтинг: 0 / 0
20.09.2018, 07:00
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39707368
QFireball
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
QFireball
Гость
Dima T,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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
Не понял, с точки зрения логики, как это реализовать и что это даст.
...
Рейтинг: 0 / 0
25.09.2018, 04:38
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39707370
QFireball
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
QFireball
Гость
ура сделал почти все! но все равно буду благодарен исправлениям и комментариям.
Мешает первая строка в таблице, оставшаяся от инициализации

Код: plsql
1.
(201800, 0)



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

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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 сделать?
...
Рейтинг: 0 / 0
25.09.2018, 05:13
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39707371
QFireball
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
QFireball
Гость
и еще: а как ходить по дням?
по месяцам вроде просто 201801 + 1 = 201802. А вот по дням с 20180131 + 1 не равно 20180201
...
Рейтинг: 0 / 0
25.09.2018, 05:15
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39707746
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Читал, читал... ниче не понял. Что ты хочешь получить в итоге???

Вообще, есть правило: если начал задумываться о цикле - значит тебе надо забыть про SQL.
Цикл делается на клиенте, и в нем посылается запрос, результат обрабатывается клиентом, и повторять до удовлетворения.
...
Рейтинг: 0 / 0
25.09.2018, 15:06
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39707773
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
White OwlЧитал, читал... ниче не понял. Что ты хочешь получить в итоге???

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

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

PPS QFireball, с месяцами тоже неверно: 201812 + 1 = 201813
...
Рейтинг: 0 / 0
25.09.2018, 15:38
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39707855
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Если я правильно догадался, то попробуй так
Код: sql
1.
2.
3.
4.
5.
6.
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
...
Рейтинг: 0 / 0
25.09.2018, 16:40
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39708080
QFireball
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
QFireball
Гость
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. Разбираюсь.
...
Рейтинг: 0 / 0
26.09.2018, 01:57
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39708127
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
QFireballЗапрос работает как надо! :) Спасибо большое! Осталось разобраться как и что происходит.
Почитай что-нибудь по языку SQL. Нет смысла сюда основы перепечатывать. SQL отличается от алгоритмических языков.
QFireballНе совсем понял, где менять strftime. Разбираюсь.
Так
Код: sql
1.
2.
3.
4.
5.
6.
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 если преобразовать к виду
Код: sql
1.
2.
    WHERE from_account_id = 13 AND to_account_id = 0 AND from_amount < 0
               AND transactions.datetime BETWEEN ... AND ...
...
Рейтинг: 0 / 0
26.09.2018, 07:12
| Ответить | Цитировать | Написать  
Использование в цикле несколько таблиц подвешивает SQL
    #39717864
QFireball
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
QFireball
Гость
Dima T,
Спасибо большое, все работает как хотел! Проблема решена
...
Рейтинг: 0 / 0
15.10.2018, 23:33
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Использование в цикле несколько таблиц подвешивает SQL
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&msg=39707855&tid=2008425]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 268ms
total:  395ms
созд. / загр.: 395ms
x
x
Закрыть


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