[Без оффтопа] Развлекаемся с SQL

[OmoN]

Сообщение от Alisher Umarov

1. Виртуальный колхоз "Кизил пиёз. Пять бригад каждый день сдают на склад пиёз в кг.
Текущий отчёт:
За день. За неделю. За месяц. За квартал. За год.

Если структура таблицы так:

Сообщение от Alisher Umarov

id, product_name, br1, br2, br3, br4, br5, date

В оракле:

PHP код:

Select 0,
       'Daily',
       To_Char(Date_Ini, 'YYYY-MM-DD') Date_Ini,
       Sum(Br_1) Br_1,
       Sum(Br_2) Br_2,
       Sum(Br_3) Br_3,
       Sum(Br_4) Br_4,
       Sum(Br_5) Br_5,
       Date_Ini Sort_Date
  From Task_1
 Group By Date_Ini
Union
Select 1,
       'Weekly',
       To_Char(Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'WWYYYY')),
               'WW-YYYY') Date_Ini,
       Sum(Br_1) Over(Partition By To_Char(Date_Ini, 'WWYYYY')) Br1,
       Sum(Br_2) Over(Partition By To_Char(Date_Ini, 'WWYYYY')) Br2,
       Sum(Br_3) Over(Partition By To_Char(Date_Ini, 'WWYYYY')) Br3,
       Sum(Br_4) Over(Partition By To_Char(Date_Ini, 'WWYYYY')) Br4,
       Sum(Br_5) Over(Partition By To_Char(Date_Ini, 'WWYYYY')) Br5,
       Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'WWYYYY')) Sort_Date
  From Task_1
Union
Select 2,
       'Monthly',
       To_Char(Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'MMYYYY')),
               'YYYY-MM') Date_Ini,
       Sum(Br_1) Over(Partition By To_Char(Date_Ini, 'MMYYYY')) Br1,
       Sum(Br_2) Over(Partition By To_Char(Date_Ini, 'MMYYYY')) Br2,
       Sum(Br_3) Over(Partition By To_Char(Date_Ini, 'MMYYYY')) Br3,
       Sum(Br_4) Over(Partition By To_Char(Date_Ini, 'MMYYYY')) Br4,
       Sum(Br_5) Over(Partition By To_Char(Date_Ini, 'MMYYYY')) Br5,
       Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'MMYYYY')) Sort_Date
  From Task_1
Union
Select 3,
       'Quarter',
       To_Char(Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'QYYYY')),
               'Q-YYYY') Date_Ini,
       Sum(Br_1) Over(Partition By To_Char(Date_Ini, 'QYYYY')) Br1,
       Sum(Br_2) Over(Partition By To_Char(Date_Ini, 'QYYYY')) Br2,
       Sum(Br_3) Over(Partition By To_Char(Date_Ini, 'QYYYY')) Br3,
       Sum(Br_4) Over(Partition By To_Char(Date_Ini, 'QYYYY')) Br4,
       Sum(Br_5) Over(Partition By To_Char(Date_Ini, 'QYYYY')) Br5,
       Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'QYYYY')) Sort_Date
  From Task_1
Union
Select 4,
       'Year',
       To_Char(Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'YYYY')),
               'YYYY') Date_Ini,
       Sum(Br_1) Over(Partition By To_Char(Date_Ini, 'YYYY')) Br1,
       Sum(Br_2) Over(Partition By To_Char(Date_Ini, 'YYYY')) Br2,
       Sum(Br_3) Over(Partition By To_Char(Date_Ini, 'YYYY')) Br3,
       Sum(Br_4) Over(Partition By To_Char(Date_Ini, 'YYYY')) Br4,
       Sum(Br_5) Over(Partition By To_Char(Date_Ini, 'YYYY')) Br5,
       Max(Date_Ini) Over(Partition By To_Char(Date_Ini, 'YYYY')) Sort_Date
  From Task_1
 Order By 9, 1 


[OmoN]

Если окончательно извращаться первую задачу можно оптимизировать так:

PHP код:

Select Distinct d.Id,
       d.Str,
       To_Char(Max(Date_Ini) Over(Partition By To_Char(Date_Ini, d.Format)),
               d.Format) Date_Ini,
       Sum(Br_1) Over(Partition By To_Char(Date_Ini, d.Format)) Br1,
       Sum(Br_2) Over(Partition By To_Char(Date_Ini, d.Format)) Br2,
       Sum(Br_3) Over(Partition By To_Char(Date_Ini, d.Format)) Br3,
       Sum(Br_4) Over(Partition By To_Char(Date_Ini, d.Format)) Br4,
       Sum(Br_5) Over(Partition By To_Char(Date_Ini, d.Format)) Br5,
       Max(Date_Ini) Over(Partition By To_Char(Date_Ini, d.Format)) Sort_Date
  From Task_1, 
       (Select 0 Id, 'Daily' Str, 'YYYY-MM-DD' Format From Dual
        Union
        Select 1 Id, 'Weekly' Str, 'YYYY-WW' Format From Dual
        Union 
        Select 2 Id, 'Monthly' Str, 'YYYY-MM' Format From Dual
        Union
        Select 3 Id, 'Quarter' Str, 'YYYY-Q' Format From Dual
        Union
        Select 4 Id, 'Year' Str, 'YYYY' Format From Dual) d
Order By 9,1 


[OmoN]

Сообщение от JackDaniels

2. Дано 1М записей;
Не самый производительный сервер;
Нужно: ORDER BY RAND() LIMIT 10;

Таблица:

PHP код:

create table TASK_2
(
  id  NUMBER,
  val NUMBER
) 


Запрос:

PHP код:

Select Id, Val
  From (Select Distinct Id, Val, Trunc(Dbms_Random.Value(1, 1000000)) Rand
          From Task_2
         Order By Rand)
 Where Rownum < 11 


ПС: Прощу прощение что все решение на оракле. У меня нет MySQL.

[Alisher Umarov]

Небольшая придирка к первой задаче, которая вылезла в практике. (Даёт хорошее представление чем отличается мышления дева и юзера)
Неделя - у юзера, это количество сданного лука за последние СЕМЬ дней.
А вот месяц, квартал и год - календарные.
Конечно это спорный вопрос. Но чтобы избежать этого, в отчетах следует разбивать вариации запросов в малых периодах.
Это легко представить когда week попадёт между двух месяцев.

[Alisher Umarov]

OmoN
Все же Dolphin прав. Такие отчёты желательно разбивать по периодам. Зачем пересчитывать данные за год если нужен всего лишь отчёт за день? А если база за десять лет?

[OmoN]

Сообщение от Alisher Umarov

Такие отчёты желательно разбивать по периодам. Зачем пересчитывать данные за год если нужен всего лишь отчёт за день? А если база за десять лет?

Я думал нужен весь отчет вместе. Какие передаваемые параметры?
Upd: Решение для первой задачи не верна. Заполнял таблицу данными на 2 года.

PHP код:

Procedure Fill_Task1 Is
    v_Date Date := To_Date('20110101', 'YYYYMMDD');
    i      Number := 0;
  Begin
    Loop
      Insert Into Task_1
        (Id, Product, Date_Ini, Br_1, Br_2, Br_3, Br_4, Br_5)
      Values
        (i,
         'Onion',
         Trunc(v_Date),
         Trunc(Dbms_Random.Value(1, 100)),
         Trunc(Dbms_Random.Value(1, 100)),
         Trunc(Dbms_Random.Value(1, 100)),
         Trunc(Dbms_Random.Value(1, 100)),
         Trunc(Dbms_Random.Value(1, 100)));
      v_Date := v_Date + 1;
      i      := i + 1;
      If (Trunc(v_Date) > To_Date('20121231', 'YYYYMMDD')) Then
        Exit;
      End If;
    End Loop;
    Commit;
  End; 


Результат не верен. Игнор.

[OmoN]

Сообщение от OmoN

Результат не верен. Игнор.

Точнее сортировка хромает.

[Nestik]

Сообщение от Alisher Umarov

OmoN
Все же Dolphin прав. Такие отчёты желательно разбивать по периодам. Зачем пересчитывать данные за год если нужен всего лишь отчёт за день? А если база за десять лет?

Ну пля так и сделайте тогда во where :date_from :date_to группировка по продукту и сумма по бригадам, и нехай юзверь со своими закидонами сам решает чё у него там день, 7 дней или неделя, месяц, квартал, год, 100 лет.

Задача вообще какая-то сумбурная.

[Alisher Umarov]

Сообщение от Nestik

Ну пля так и сделайте тогда во where :date_from :date_to группировка по продукту и сумма по бригадам, и нехай юзверь со своими закидонами сам решает чё у него там день, 7 дней или неделя, месяц, квартал, год, 100 лет.

Задача вообще какая-то сумбурная.

Ещё один типичный пример dev подхода против юзерского.
Доказано много раз - юзеры чрезвычайно редко знают что им нужно в конечном виде. К этому давно пора привыкнуть.
(Но в данном примере именно показано что к датам в SQL нужно относится осторожно)

[OmoN]

В предедущем запросе формат недели YYYY-WW дал неправильные результаты. Он не показывал первые 4 недели года. Причину не знаю. Если формат недели YYYY.WW работает нормально. И тут добавил фильтр по дата начало и дата окончание отчетного периода.

PHP код:

Select Distinct d.Id,
       d.Str,
       To_Char(Max(Date_Ini) Over(Partition By To_Char(Date_Ini, d.Format)),
               d.Format) Date_Ini,
       Sum(Br_1) Over(Partition By To_Char(Date_Ini, d.Format)) Br1,
       Sum(Br_2) Over(Partition By To_Char(Date_Ini, d.Format)) Br2,
       Sum(Br_3) Over(Partition By To_Char(Date_Ini, d.Format)) Br3,
       Sum(Br_4) Over(Partition By To_Char(Date_Ini, d.Format)) Br4,
       Sum(Br_5) Over(Partition By To_Char(Date_Ini, d.Format)) Br5,
       Max(Date_Ini) Over(Partition By To_Char(Date_Ini, d.Format)) Sort_Date
  From Task_1, 
       (Select 0 Id, 'Daily' Str, 'YYYY-MM-DD' Format From Dual
        Union
        Select 1 Id, 'Weekly' Str, 'YYYY.WW' Format From Dual
        Union 
        Select 2 Id, 'Monthly' Str, 'YYYY-MM' Format From Dual
        Union
        Select 3 Id, 'Quarter' Str, 'YYYY-Q' Format From Dual
        Union
        Select 4 Id, 'Year' Str, 'YYYY' Format From Dual) d
  Where Trunc(Date_Ini) >= To_Date('20110101','YYYYMMDD')
  And Trunc(Date_Ini) < To_Date('20130101','YYYYMMDD')
Order By 9,1