Перейти из форума на сайт.

НовостиФайловые архивы
ПоискАктивные темыТоп лист
ПравилаКто в on-line?
Вход Забыли пароль? Первый раз на этом сайте? Регистрация
Компьютерный форум Ru.Board » Компьютеры » Прикладное программирование » SQL запрос

Модерирует : ShIvADeSt

 Версия для печати • ПодписатьсяДобавить в закладки
Страницы: 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

Открыть новую тему     Написать ответ в эту тему

NiXXX



Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Steepe_Hare, в том году еще работало. В этом с SQLite не работал

Всего записей: 365 | Зарегистр. 06-05-2003 | Отправлено: 17:48 24-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Steepe_Hare

Цитата:
А upper(column)  работает на SQLite?

Должно:
https://sqlite.org/lang_corefunc.html#upper
 

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 20:44 24-10-2017 | Исправлено: Mic777, 20:45 24-10-2017
Steepe_Hare



Advanced Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Mic777
 
Спасибо!
А можно такой ламерский вопрос? Просто я только вхожу в мир SQLite.
 
На SQLite возможно делать выборку по memo полю? (то есть не по VARCHAR, а по TEXT) ?

Всего записей: 1162 | Зарегистр. 27-10-2001 | Отправлено: 13:51 25-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Steepe_Hare

Цитата:
На SQLite возможно делать выборку по memo полю? (то есть не по VARCHAR, а по TEXT) ?

Не работал с ним, но судя по докам instr понимает блобы, а вообще курил бы в сторону
https://sqlite.org/fts3.html
 

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 15:42 25-10-2017
NiXXX



Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Продолжаю упражнения с ораклом
Есть у меня табличка. Хочу вытащить из нее среднее время выполнения запроса по суткам. Написал вот такой запрос. Он возвращает, что надо, но как-то не красив... Можно сделать тоже самое, но красиво?

Всего записей: 365 | Зарегистр. 06-05-2003 | Отправлено: 16:42 25-10-2017 | Исправлено: NiXXX, 16:44 25-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
NiXXX

Цитата:
Можно сделать тоже самое, но красиво?

Главное чтобы план был красивый, текст запроса вторичен.
Что именно не устраивает?
А в общем можно смотреть в сторону аналитических функций.

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 20:34 25-10-2017
NiXXX



Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Mic777

Цитата:
Что именно не устраивает?

Select от select'а как-то не комильфо на мой взгляд.

Всего записей: 365 | Зарегистр. 06-05-2003 | Отправлено: 12:28 26-10-2017 | Исправлено: NiXXX, 12:28 26-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
NiXXX

Цитата:
Select от select'а как-то не комильфо на мой взгляд.

Subqueries - нормальное явление, вполне, а зачастую и вообще никак без них.
Иногда и оптимизатор приходится еще хинтом просить, чтобы он подзапрос сначала выполнил и не умничал
В общем тут криминального ничего такого вроде не видно.

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 13:36 26-10-2017
Dronton2

Full Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

Цитата:
Select от select'а как-то не комильфо на мой взгляд.

У меня вложенность запросов иногда бывает более 5 уровней. Работает быстро.
Если не нравятся вложенные запросы, попробуйте использовать WITH: https://oracle-base.com/articles/misc/with-clause

Всего записей: 460 | Зарегистр. 27-06-2005 | Отправлено: 10:00 27-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

Цитата:
попробуйте использовать WITH

хорошая конструкция, но на продакшине без техподдержки Oracle (читай патчей) стремно.
Чревато нарваться на ORA-600.

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 12:29 27-10-2017
Dronton2

Full Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Есть такая задачка (система бонусов в магазине):
есть 2 таблицы:
1. начисления бонусов покупателю.
   Поля: дата начисления, начисленная сумма
2. списание бонусов с покупателя.
   Поля: дата списания, списанная сумма
Требуется вычислить, сколько бонусов есть у покупателя на определённую дату.
Задача решается тривиально (одним или двумя SELECTами, кому как нравится).
 
Теперь усложним задачу: пусть срок действия бонусов ограничен. Например, через 90 дней после начисления, та часть бонуса, которая не была списана покупателем, сгорает.
Получилось решить её с помощью двух курсоров (один - по начисленным бонусам, другой - по списанным и сгоревшим) и одной переменной в PL/SQL.
Можно ли решить её в чистом SQL (аналитическими функциями или как-то ещё)?
PS: Oracle 11.2

Всего записей: 460 | Зарегистр. 27-06-2005 | Отправлено: 16:36 27-10-2017 | Исправлено: Dronton2, 16:39 27-10-2017
Alexzzy

Advanced Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Dronton2
Где "90 дней" хранятся то? По правильному, как мне видится - срок действия должен быть в первой таблице, иначе предвидится бардак. Связь по начисленным и списанным бонусам должна присутствовать.
 
P.S. Структура БД (частичная, по данному вопросу)  не описана полностью.
Создалось впечатление, что надо не извращаться в запросах, а правильно проектировать...

Всего записей: 1474 | Зарегистр. 10-07-2013 | Отправлено: 20:32 27-10-2017 | Исправлено: Alexzzy, 23:51 27-10-2017
Dronton2

Full Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
"90 дней" пока нигде не хранятся. По постановке задачи - это константа. Но, зная, что ничего постоянным не бывает, хранимая процедура сделана так, что время действия бонусов можно будет изменять. И храниться они будут действительно, в первой таблице. И переход на этот вариант - тривиальнй: добавление поля в первую таблицу и замена константы в запросе на название соответствующего поля.

Цитата:
Связь по начисленным и списанным бонусам должна присутствовать.
Покупатель покупает товары, зарабатывая покупками бонусы, Этот факт записывается в первую таблицу. Накопленные бонусы покупатель по желанию может реализовать в любой момент времени. Факт реализации бонусов записывается во вторую таблицу. Требуется получать ответ на вопрос: сколько бонусов у покупателя существует на определённый момент времени? Какие связи вы хотите сюда добавить, и зачем? Задача решается хранимой процедурой с двумя несложными курсорами и парой циклов по курсорам. Общее количество итераций в циклах не превышает количества записей во второй таблице плюс удвоенного количества записей в первой таблице из примера.

Цитата:
Структура БД (частичная, по данному вопросу)  не описана полностью.
Описано только то, что имеет отношение к вопросу. Всякие ID покупателя, алгоритмы начисления, списания и другие отвлекающие элементы не описаны. Я просил поделиться идеями решения задачи в том виде, в котором она сформулирована.
Зачем мне это нужно: обычно, с аналитическими запросами получается меньше бойлерплейт кода. Если план такого запроса будет удовлетворительным, то можно его включать в свой арсенал, т.к. подобные задачи иногда случаются.
 

Всего записей: 460 | Зарегистр. 27-06-2005 | Отправлено: 11:54 30-10-2017 | Исправлено: Dronton2, 11:55 30-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Dronton2

Цитата:
Можно ли решить её в чистом SQL (аналитическими функциями или как-то ещё)?  

 
Если я правильно понял условие, то как-то так:

Код:
 
with t as (
select to_date('01.01.2001','dd.mm.yyyy') date_oper, 100 bonus_value from dual union all
select to_date('02.01.2001','dd.mm.yyyy') date_oper, 50 bonus_value from dual union all
select to_date('02.01.2001','dd.mm.yyyy') date_oper, -30 bonus_value from dual union all
select to_date('03.01.2001','dd.mm.yyyy') date_oper, 20 bonus_value from dual union all
select to_date('03.01.2001','dd.mm.yyyy') date_oper, -10 bonus_value from dual union all
select to_date('04.01.2001','dd.mm.yyyy') date_oper, 80 bonus_value from dual union all
select to_date('04.01.2001','dd.mm.yyyy') date_oper, -5 bonus_value from dual union all
select to_date('05.01.2001','dd.mm.yyyy') date_oper, -70 bonus_value from dual
)
 
   
select t.date_oper,
       t.bonus_value,  
       -- сумма начислений бонусов за период (для примера текущий + предыдущий дни)
       sum(
         case  
           when t.bonus_value > 0 then t.bonus_value
           else 0
         end
       -- тут вместо 1 Ваша "константа" должна быть
       ) over (order by t.date_oper asc range 1 preceding)  
       +
       -- сумма списаний бонусов за день
       case  
         when t.bonus_value < 0 then t.bonus_value
         else 0
       end
       as sum_bonus_on_day
       
  from t;
 

 
sum_bonus_on_day - сумма бонусов на конец дня так сказать (на момент после текущей операции - наверное правильней сказать)
ну и при условии, что ПО следит что кол-во списываемых бонусов не превышает это значение.
 
DATE_OPER BONUS_VALUE SUM_BONUS_ON_DAY    
01.01.2001 100 100    
02.01.2001 50 150    
02.01.2001 -30 120    
03.01.2001 20 70    
03.01.2001 -10 60    
04.01.2001 80 100    
04.01.2001 -5 95    
05.01.2001 -70 10  

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 12:16 30-10-2017 | Исправлено: Mic777, 13:25 30-10-2017
Dronton2

Full Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Спасибо, Mic777. Но, к сожалению - не работает: заменим в ваших данных предпоследнюю строчку с
select to_date('04.01.2001','dd.mm.yyyy') date_oper, -5 bonus_value from dual union all
на  
select to_date('04.01.2001','dd.mm.yyyy') date_oper, -25 bonus_value from dual union all
(списываем 4 января не 5 бонусов, а 25). Результат вашего алгоритма будет таким:
DATE_OPER BONUS_VALUE SUM_BONUS_ON_DAY    
2001-01-01 100 100    
2001-01-02 50 150    
2001-01-02 -30 120    
2001-01-03 20 70    
2001-01-03 -10 60    
2001-01-04 80 100    
2001-01-04 -25 75    
2001-01-05 -70 10

Но после последнего списания, остаток бонусов должен быть не 10, а 5.
Рассуждения такие:
На 4 января накоплено 100 бонусов (20 за 3 января и 80 за 4 января).
Затем, 4 января списываем 25 бонусов ( 20 накопленных 3 января и 5 накопленных 4 января).
Затем, 5 января списываем бонусы, накопленные 3 и 4 января. Но из накопленных 4 января бонусов, 5 бонусов потратили накануне, поэтому их осталось не 80, а 75.
После списания 70 бонусов, должно остаться 5.
 
PS. Кажется, что решение где-то рядом (если оно, конечно, существует вне написания хранимой процедуры)
PPS и Off: а как вы вставили в пост результат в виде таблицы? Из SQL Fiddle копипастом не получается.

Всего записей: 460 | Зарегистр. 27-06-2005 | Отправлено: 13:43 30-10-2017 | Исправлено: Dronton2, 13:59 30-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Dronton2
Не согласен

Цитата:
Затем, 5 января списываем бонусы, накопленные 3 и 4 января. Но из накопленных 4 января бонусов, 5 бонусов потратили накануне, поэтому их осталось не 80, а 75.  
После списания 70 бонусов, должно остаться 5.  

 
Как я сказал это пример для двух дней т.е. у нас окно в текущий и предыдущий день,
поэтому 5 января у нас есть только бонусы от 4-го и 5-го если начислялись - а за 3-е они "сгорели" если судить из Вашей постановки задачи.
 
т.е. мы не можем списывать бонусы 5-го января, накопленные и за 3-е в т.ч.

Цитата:
Затем, 5 января списываем бонусы, накопленные 3 и 4 января.

 
Идея в том, чтобы не считать какие именно бонусы списываются (когда они были накоплены - главное что они действуют).
И считать остаток (с учетом окна в котором действуют) бонусов до операции и отнять количество бонусов списанных во время операции.
 
 

Цитата:
а как вы вставили в пост результат в виде таблицы?

Без всякой магии, ручки и блокнот

Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 14:11 30-10-2017 | Исправлено: Mic777, 14:18 30-10-2017
Dronton2

Full Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Mic777

Цитата:
5 января у нас есть только бонусы от 4-го и 5-го если начислялись - а за 3-е они "сгорели" если судить из Вашей постановки задачи.

Наверное, я сумбурно объяснил. Попробую по-другому:
Да, 5 января мы учитываем бонусы только за 4 и 5 января. Но, к утру 5 января мы имеем только 75 (а не 80) бонусов из 4 января, т.к. из 80 бонусов, начисленных 4 января 5 бонусов потратили в тот же день (4 января), а именно:
4 января списали всего 25 бонусов, из них: 20 бонусов, начисленных 3 января и 5 бонусов, начисленных 4 января.
Значит, от 80 бонусов, начисленных 4 января, к утру 5 января осталось только 75 бонусов.
А после вычета 70 бонусов, остаётся только 5.
Ваши исходные данные можно упростить, удалив 1,2,3 и 5 строки, оставив так:

Код:
with t as (  
select to_date('03.01.2001','dd.mm.yyyy') date_oper, 20 bonus_value from dual union all  
select to_date('04.01.2001','dd.mm.yyyy') date_oper, 80 bonus_value from dual union all  
select to_date('04.01.2001','dd.mm.yyyy') date_oper, -25 bonus_value from dual union all  
select to_date('05.01.2001','dd.mm.yyyy') date_oper, -70 bonus_value from dual  
)

Результат вашего кода будет:
DATE_OPER BONUS_VALUE SUM_BONUS_ON_DAY    
2001-01-03 20 20    
2001-01-04 80 100    
2001-01-04 -25 75    
2001-01-05 -70 10

А должно остаться не 10, а 5. Ну, хотя бы потому, что всего начислили 20+80=100, а списали 25+70=95. И никак не может остаться 10 бонусов после вычитания 100-95, даже если ни один бонус не успел сгореть.

Всего записей: 460 | Зарегистр. 27-06-2005 | Отправлено: 14:43 30-10-2017
Alexzzy

Advanced Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Dronton2
Связь между таблицами позовляет:
1. Иметь чёткую и самодостаточную информацию по начислениям и списаниям. По ней видно как списывается.
2. Проще делать выборку данных.
 
Недостаток - слегка усложняется алгоритм списания. Единократно списывается по каждому начислению отдельно.
 
Реализуется примерно так.
 
Таблица начисления - id_bonus (первичный ключ), дата начисления, дата окончания срока, сумма начисления.
Таблица списания - id_bonus (внешний ключ, отношение один ко многим), дата списания, сумма списания.
 
Получение общего количества бонусов на дату:

Код:
select sum(b.СуммаНачисления - (select sum(s.СуммаСписания) from ТаблицаCписания s where s.id_bonus = b.id_bonus))
from ТаблицаНачисления b
where Дата between b.ДатаНачисления and b.ДатаОкончанияСрока

 
Просто так же получить количество бонусов на дату в разрезе начислений бонусов и по этим данным производить списание...

Всего записей: 1474 | Зарегистр. 10-07-2013 | Отправлено: 14:52 30-10-2017 | Исправлено: Alexzzy, 17:14 30-10-2017
Mic777

Junior Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Dronton2

Цитата:
А должно остаться не 10, а 5.

Я понял Вас.
Акцентировал внимание на:  

Цитата:
Затем, 5 января списываем бонусы, накопленные 3 и 4 января.

но пропустил:

Цитата:
списываем 4 января не 5 бонусов, а 25


Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 15:06 30-10-2017
Dronton2

Full Member
Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Alexzzy, к сожалению, я живу не в таком счастливом мире, как вы.
Во-первых, к вашему коду нужно добавить код, который рассчитывает сгоревшие бонусы (и, судя по всему, записывает их в таблицу списания). Таблица списания (с отношением один ко многим) будет полностью расчётной, которая будет рассчитываться на основании данных о списании, заводимых пользователями.
А при постоянных ошибках этих самых пользователей, обнаруживаемых иногда через несколько месяцев, и влияющих на всё (не тому покупателю записали/списали, не то количество записали/списали, не той датой записали/списали), поддержка актуальности этой расчётной таблицы становится слишком сложной.

Всего записей: 460 | Зарегистр. 27-06-2005 | Отправлено: 15:37 30-10-2017
Открыть новую тему     Написать ответ в эту тему

Страницы: 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

Компьютерный форум Ru.Board » Компьютеры » Прикладное программирование » SQL запрос


Реклама на форуме Ru.Board.

Powered by Ikonboard "v2.1.7b" © 2000 Ikonboard.com
Modified by Ru.B0ard
© Ru.B0ard 2000-2024

BitCoin: 1NGG1chHtUvrtEqjeerQCKDMUi6S6CG4iC

Рейтинг.ru