GROUP BY без групповых функций в MySQL

#mysql #sql #group_by

                    
Какую информацию выдаёт MySQL, если делать GROUP BY без групповых функций?

например:

id|name|count|date
-------------------------
1 | aa |  1  |null
1 | bb |null |2007-12-12
1 |null|  2  |2015-10-12


GROUP BY id

будет здесь какая-нибудь закономерность в выдаче в полях name, count и date или туда
попадают случайные значения из выборки?

P.S. тот же PostgreSQL просто выдаст ошибку.
    

Ответы

Ответ 1

При исполнении запроса есть некий порядок обработки строк.
Заданный либо разработчиком, либо на усмотрение оптимизатора.
Так вот будет выдана строка, которая была обработана первой в группе.

Следующий запрос:

SELECT *
FROM(
  SELECT *
  FROM(
              SELECT 1 id, 1 OrderBy, 10 value
    UNION ALL SELECT 1 id, 2 OrderBy, 20 value
    UNION ALL SELECT 1 id, 3 OrderBy, 30 value
  )T
  ORDER BY OrderBy
)T
GROUP BY id


Вернёт:

id OrderBy value
1  1       10


Однако такой запрос:

SELECT *
FROM(
  SELECT *
  FROM(
              SELECT 1 id, 1 OrderBy, 10 value
    UNION ALL SELECT 1 id, 2 OrderBy, 20 value
    UNION ALL SELECT 1 id, 3 OrderBy, 30 value
  )T
  ORDER BY OrderBy DESC
)T
GROUP BY id


Вернёт уже:

id OrderBy value
3  3       30


Но, повторюсь, сортировку может выбрать оптимизатор какую угодно, поэтому в общем
случае, предсказать какую строку "выберет" оптимизатор невозможно.

Т.е. да, теоретически, если задать уникальную сортировку, можно использовать данную
особенность MySQL осознанно. Но на свой страх и риск. Поскольку данное поведение не
описано в документации и может измениться в будущих версиях MySQL сервера.


Ответ 2

Ответ из комментариев

Будет выдана случайная строка, если быть совсем точном, то строка, которая будет
отобрана, конечно, не случайна. Однако, ввиду того, что по мере внесения изменений
в таблицу, будут выдаваться разные строки, то для «простого пользователя» это будет
выглядеть именно как «случайно выбранная строка».

Это не типичное поведение для СУБД, так ведет себя пожалуй только MySQL. PostgreSql,
MS Sql и Oracle выдадут ошибку потому, что это противоречит стандарту SQL. В MySql
же (если не задавать специальных параметров) в данном случае используется некий расширенный
стандарт, который позволяет так делать. Считается, что если указанная колонка не перечислена
в GROUP BY, то все ее значения в пределах группы одинаковы и поэтому не важно из какой
именно строки оно будет возвращено.

Получение строки значений из groupby

#python #pandas #dataframe #group_by

Есть DataFrameGroupby со следующими данными:

                       last  vol
datetime                        
2013-07-23 10:00:00  112450   49
2013-07-23 10:00:00  112440   67
2013-07-23 10:00:00  112430   93
2013-07-23 10:00:00  112420   52
2013-07-23 10:00:00  112410   63

                       last  vol
datetime                        
2013-07-23 10:01:00  112690   17
2013-07-23 10:01:00  112680   59
2013-07-23 10:01:00  112670  226
2013-07-23 10:01:00  112660  184
2013-07-23 10:01:00  112650  289


Сгруппированные по уровню индекса:

blocks_group = datetime_group.groupby(level=0)


Как получить целую строку из каждой группы с максимальным значением, а не только
значения столбца vol?
    

Ответы

Ответ 1

Исходный DataFrame:

In [47]: df
Out[47]:
                       last  vol
datetime
2018-08-31 10:00:00  112450   49
2018-08-31 10:00:00  112440   67
2018-08-31 10:00:00  112430   93
2018-08-31 10:00:00  112420   52
2018-08-31 10:00:00  112410   63
2018-08-31 10:01:00  112690   17
2018-08-31 10:01:00  112680   59
2018-08-31 10:01:00  112670  226
2018-08-31 10:01:00  112660  184
2018-08-31 10:01:00  112650  289


Решение:

In [48]: df.groupby(level=0, as_index=False).apply(lambda x: x.nlargest(1, 'vol'))
Out[48]:
                         last  vol
  datetime
0 2018-08-31 10:00:00  112430   93
1 2018-08-31 10:01:00  112650  289


Ещё один, менее идиоматичный, вариант:

In [51]: df.sort_values('vol').groupby(level=0).tail(1)
Out[51]:
                       last  vol
datetime
2018-08-31 10:00:00  112430   93
2018-08-31 10:01:00  112650  289

Столбец по сгруппированным данным Pandas

#python #pandas #dataframe #group_by

Имеется Pandas DataFrame, например:

  city  col1  
0  nsk    15
1  nsk    17
2  nsk    22
3  vdk    11
4  vdk     9


Требуется добавить столбец, в котором будут суммы значений col1, сгруппированные
по столбцу city. Например:

  city  col1  col2
0  nsk    15    54
1  nsk    17    54
2  nsk    22    54
3  vdk    11    20
4  vdk     9    20

    

Ответы

Ответ 1

In [434]: df['col2'] = df.groupby('city')['col1'].transform('sum')

In [435]: df
Out[435]:
  city  col1  col2
0  nsk    15    54
1  nsk    17    54
2  nsk    22    54
3  vdk    11    20
4  vdk     9    20

Получить предпоследний заказ для клиента

#python #pandas #dataframe #group_by

У меня есть dataframe с некоторым количеством заказов, id клиента и датой заказа.
Мне нужно посчитать последний выполненный заказ по клиенту для каждой строки

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

import pandas as pd
data=pd.DataFrame({'ID': [ 133853.0,155755.0,149331.0,337270.0,
  775727.0,200868.0,138453.0,738497.0,666802.0,697070.0,128148.0,1042225.0,
  303441.0,940515.0,143548.0],
 'CLIENT':[ 235632.0,231562.0,235632.0,231562.0,734243.0,
   235632.0,235632.0,734243.0,231562.0,734243.0,235632.0,734243.0,231562.0,
   734243.0,235632.0],
 'DATE_START': [ ('2017-09-01 00:00:00'),
   ('2017-10-05 00:00:00'),('2017-09-26 00:00:00'),
   ('2018-03-23 00:00:00'),('2018-12-21 00:00:00'),
   ('2017-11-23 00:00:00'),('2017-09-08 00:00:00'),
   ('2018-12-12 00:00:00'),('2018-11-21 00:00:00'),
   ('2018-12-01 00:00:00'),('2017-08-22 00:00:00'),
   ('2019-02-06 00:00:00'),('2018-02-20 00:00:00'),
   ('2019-01-20 00:00:00'),('2017-09-17 00:00:00')]})
data.groupby('CLIENT').apply(lambda x:max(x['ID']))


В идеале нужно, чтобы было тоже количество строк в dataframe, и был еще столбец в
котором указано значение id заказа, который был последним перед текущим заказом.
    

Ответы

Ответ 1

Попробуйте так:

data['prev_id']= data.sort_values('DATE_START').groupby('CLIENT')['ID'].transform(lambda
x: x.shift())


Или так:

data['prev_id']= data.sort_values('DATE_START').groupby('CLIENT').shift()['ID']

Группировка кол-ва выполненных задач по сотрудникам

#python #python_3x #pandas #dataframe #group_by

Как сгруппировать корректно данные? 

Имеется датафрейм со статусом завершенных заданий по каждому аналитику и типу задач:

df1

    ID     Board   Analyst   Status   Crea_d       Fin_d
    46258  RUCRA   Ivanov    open     2019-07-10    NaT
    2345   RUCRA   Ivanov    close    2019-07-11   2019-07-11
    46218  RUCRA   Ivanov    close    2019-07-11   2019-07-11
    3087   RUCRA   Sidorov   open     2019-07-22    NaT
    2367   BV      Petrov    open     2019-07-25    NaT
    2985   GRADE   Petrov    close    2019-07-05   2019-07-05 
    20987  GRADE   Ivanov    close    2019-07-11   2019-07-12
    2396   BV      Sidorov   open     2019-07-29     NaT


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

grouped_df:

      Board   Analyst   Status   Count       
      RUCRA   Ivanov    open     1    
      RUCRA   Ivanov    close    2   
      RUCRA   Sidorov   open     1    
      BV      Petrov    open     1    
      GRADE   Petrov    close    1    
      GRADE   Ivanov    close    1
      BV      Sidorov   open     1     


Я что-то похожее сделал, но где-то нарушена логика, где не могу разобраться:

grouped_df: = (df1.groupby(['Board','Analyst','Status', pd.Grouper(key='Fin_d', freq='M')],
as_index=False)['ID'].count())


Просто дальше я хочу строить графики по каждому аналитику, сколько он по дням выполняет
заданий (bars) с трендовой линией, но то ли из-за ошибки в коде или нарушения логики,
ничего не выходит.
    

Ответы

Ответ 1

Если я правильно понял:

In [5]: df1.groupby(["Board", "Analyst", "Status"]).size().reset_index(name="Count")
Out[5]:
   Board  Analyst Status  Count
0     BV   Petrov   open      1
1     BV  Sidorov   open      1
2  GRADE   Ivanov  close      1
3  GRADE   Petrov  close      1
4  RUCRA   Ivanov  close      2
5  RUCRA   Ivanov   open      1
6  RUCRA  Sidorov   open      1




или так:

In [11]: df1.groupby(["Board", "Analyst", "Status", pd.Grouper(key='Crea_d', freq='MS')]).size().reset_index(name="Count")
Out[11]:
   Board  Analyst Status     Crea_d  Count
0     BV   Petrov   open 2019-07-01      1
1     BV  Sidorov   open 2019-07-01      1
2  GRADE   Ivanov  close 2019-07-01      1
3  GRADE   Petrov  close 2019-07-01      1
4  RUCRA   Ivanov  close 2019-07-01      2
5  RUCRA   Ivanov   open 2019-07-01      1
6  RUCRA  Sidorov   open 2019-07-01      1

Трансформация DataFrame

#python #pandas #dataframe #group_by #статистика

Есть 2 pd.DataFrame:

a = pd.DataFrame([[1, 20], [2, 25], [3, 24], [1, 22], [2, 29]], columns=['type', 'size'])
b = pd.DataFrame([[1, 19], [1, 21], [2, 24], [4, 21]], columns=['type', 'size'])


В каждый DataFrame хочу добавить столбцы следующего типа

a['amount_mean_type'] = a['size'] / a.groupby('type')['size'].transform('mean')
a['amount_std_type'] = a['size'] / a.groupby('type')['size'].transform('std')


Вопрос в том, как мне сделать так, чтобы в b['amount_mean_type'] и b['amount_std_type']
я использовал mean и std по группам из a, а по тем категориям, которых нет в a, использовать
mean и std по всему сету (если это корректно). 

Что-то вроде этого: 

b['amount_mean_type'] = b['size'] / a.groupby('type')['size'].transform('mean')
b['amount_std_type'] = b['size'] / a.groupby('type')['size'].transform('std')


Но только с теми условиями, которые описал выше

UPD: пример фреймов на выходе

Для a все просто:

a['amount_mean_type'] = a['size'] / a.groupby('type')['size'].transform('mean')
a['amount_std_type'] = a['size'] / a.groupby('type')['size'].transform('std')

  type size amount_mean_type amount_std_type
0   1   20  0.952381    14.142136
1   2   25  0.925926    8.838835
2   3   24  1.000000    NaN 
3   1   22  1.047619    15.556349
4   2   29  1.074074    10.253048


для b приходится прописать вручную каждую операцию

b.loc[0, 'amount_mean_type'] = b.loc[0, 'size'] / a[a['type'] == 1]['size'].mean()
b.loc[1, 'amount_mean_type'] = b.loc[1, 'size'] / a[a['type'] == 1]['size'].mean()
b.loc[2, 'amount_mean_type'] = b.loc[2, 'size'] / a[a['type'] == 2]['size'].mean()
b.loc[3, 'amount_mean_type'] = b.loc[3, 'size'] / a['size'].mean()

b.loc[0, 'amount_std_type'] = b.loc[0, 'size'] / a[a['type'] == 1]['size'].std()
b.loc[1, 'amount_std_type'] = b.loc[1, 'size'] / a[a['type'] == 1]['size'].std()
b.loc[2, 'amount_std_type'] = b.loc[2, 'size'] / a[a['type'] == 2]['size'].std()
b.loc[3, 'amount_std_type'] = b.loc[3, 'size'] / a['size'].std()

  type size amount_mean_type amount_std_type
0   1   19  0.904762    13.435029
1   1   21  1.000000    14.849242
2   2   24  0.888889    8.485281
3   4   21  0.875000    6.192562 # Считается по всей переменной 'type', так как категории
(4) нет в `a`


Фреймы a и b можно рассматривать как train и test соответственно
    

Ответы

Ответ 1

tmp = a.groupby('type')['size'].agg(["mean", "std"])

res = (b.assign(x=b["type"].map(tmp["mean"]).fillna(a["size"].mean()))
        .eval("amount_mean_type = size / x")
        .drop(columns="x"))

res = (res.assign(x=b["type"].map(tmp["std"]).fillna(a["size"].std()))
          .eval("amount_std_type = size / x")
          .drop(columns="x"))


результат:

In [28]: res
Out[28]:
   type  size  amount_mean_type  amount_std_type
0     1    19          0.904762        13.435029
1     1    21          1.000000        14.849242
2     2    24          0.888889         8.485281
3     4    21          0.875000         6.192562

Вычисление данных в сгруппированном Data Frame

#python #python_3x #pandas #dataframe #group_by

Есть два DF:

df_estim = pd.DataFrame({'estim': ['est1', 'est2', 'est3'],
                   'title': ['title1', 'title2', 'title3'],
                    'key': ['key1', 'key2', 'key3'],                   
                   'mount': [300.15, 350.85, 400.47],
                   'equip': [870.35, 1750.59, 1830.80]})
df_estim


    estim   title   key     mount   equip
0   est1    title1  key1    300.15  870.35
1   est2    title2  key2    350.85  1750.59
2   est3    title3  key3    400.47  1830.80



df_equip = pd.DataFrame({'sys_2': ['sys1', 'sys1', 'sys1', 'sys1', 'sys1', 'sys1',
'sys2', 'sys2', 'sys2', 'sys2',
                                   'sys2', 'sys2', 'sys2', 'sys2', 'sys3', 'sys3',
'sys3', 'sys3', 'sys3', 'sys3',
                                  'sys3', 'sys3'],                    
                    'block_2': [0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 
                                1, 2, 2, 2, 0, 0, 0, 1, 1, 2,
                               2, 2],
                    'kks_2': ['kks1', 'kks2', 'kks3', 'kks4', 'kks5', 'kks6', 'kks7',
'kks8', 'kks9', 'kks10',
                              'kks11', 'kks12', 'kks13', 'kks14', 'kks15', 'kks16',
'kks17', 'kks18', 'kks19', 'kks20',
                             'kks21', 'kks22'],
                    'key': ['key1', 'key1', 'key2', 'key2', 'key3', 'key3', 'key1',
'key1', 'key2', 'key3', 
                            'key3', 'key2', 'key1', 'key1', 'key2', 'key2', 'key3',
'key2', 'key3', 'key2',
                           'key3', 'key3'],
                    'price_2': [100.10, 110.10, 120.10, 130.10, 140.10, 150.10, 160.10,
170.10, 180.10, 190.10, 
                                200.10, 210.10, 220.10, 230.10, 240.10, 250.10, 260.10,
270.10, 280.10, 290.10, 
                                300.10, 310.10]
                           })
df_equip[:5]


    sys_2 block_2 kks_2 key     price_2
0   sys1    0   kks1    key1    100.1
1   sys1    0   kks2    key1    110.1
2   sys1    0   kks3    key2    120.1
3   sys1    0   kks4    key2    130.1
4   sys1    0   kks5    key3    140.1


Для дальнейшего объяснения делаю объединение:

merge_df = pd.merge(df_equip, df_estim,  how='inner', left_on='key', right_on='key', 
                    left_index=False)
merge_df[:5]


    sys_2 block_2 kks_2 key     price_2 estim   title   mount   equip
0   sys1    0   kks1    key1    100.1   est1    title1  300.15  870.35
1   sys1    0   kks2    key1    110.1   est1    title1  300.15  870.35
2   sys2    1   kks7    key1    160.1   est1    title1  300.15  870.35
3   sys2    1   kks8    key1    170.1   est1    title1  300.15  870.35
4   sys2    2   kks13   key1    220.1   est1    title1  300.15  870.35


Нужно из полученных вспомогательных данных (ниже):

merge_df.groupby(['estim', 'equip'])['price_2'].sum()

estim  equip  
est1   870.35      990.6
est2   1750.59    1690.8
est_3  1830.80    1830.8


вычислить отношение по каждой записи estim между данными колонки equip и ['price_2'].sum(),
и поместить их в колонку coeff (например, для est1: 870.35/990.6 = 0.8786). Коэффициент
необходим для расчета данных в новой колонке estim_sys. Порядок расчета для est1: сумма
['price_2'].sum() / коэффициент для est1 = 0.8786. Итого: 239.24:

merge_df.groupby(['sys_2', 'block_2', 'estim'])['price_2'].sum()

sys_2  block_2  estim   estim_sys
sys1   0        est1     239.24       210.2
                est2                  250.2
                est3                  290.2
sys2   1        est1                  330.2
                est2                  180.1
                est3                  390.2
       2        est1                  450.2
                est2                  210.1
sys3   0        est2                  490.2
                est3                  260.1
       1        est2                  270.1
                est3                  280.1
       2        est2                  290.1
                est3                  610.2



И еще прошу подсказать, как сохранить результаты (['price_2'].sum()) в стационарную
колонку, например cost_equip, что бы к ней можно было обращаться.
    

Ответы

Ответ 1

Попробуйте так:

m = merge_df.copy()         
m["coeff"] = m["equip"] / m.groupby(['estim', 'equip'])['price_2'].transform("sum")
res = (m
       .groupby(['sys_2', 'block_2', 'estim'])
       .apply(lambda x: x["price_2"].sum() / x["coeff"])
       .reset_index(name="estim_sys"))




In [32]: res
Out[32]:
   sys_2  block_2 estim  level_3   estim_sys
0   sys1        0  est1        0  239.241822
1   sys1        0  est1        1  239.241822
2   sys1        0  est2        6  241.654619
3   sys1        0  est2        7  241.654619
4   sys1        0  est3       14  290.200000
5   sys1        0  est3       15  290.200000
6   sys2        1  est1        2  375.821359
7   sys2        1  est1        3  375.821359
8   sys2        1  est2        8  173.948829
9   sys2        1  est3       16  390.200000
10  sys2        1  est3       17  390.200000
11  sys2        2  est1        4  512.400896
12  sys2        2  est1        5  512.400896
13  sys2        2  est2        9  202.924203
14  sys3        0  est2       10  473.457611
15  sys3        0  est2       11  473.457611
16  sys3        0  est3       18  260.100000
17  sys3        1  est2       12  260.874951
18  sys3        1  est3       19  280.100000
19  sys3        2  est2       13  280.191867
20  sys3        2  est3       20  610.200000
21  sys3        2  est3       21  610.200000





  Как образовалась колонка "level_3"?


в столбце level_3 - значения оригинального индекса из merge_df DataFrame.


  Как правильно добавить колонку "estim_sys" из "res" в "m"?


здесь мы как раз можем использовать столбец "level_3", чтобы Pandas смог выровнять
данные по индексам при присваивании:

In [42]: m["estim_sys"] = res.set_index("level_3")["estim_sys"]

In [43]: m
Out[43]:
   sys_2  block_2  kks_2   key  price_2  ...   title   mount    equip     coeff 
 estim_sys
0   sys1        0   kks1  key1    100.1  ...  title1  300.15   870.35  0.878609 
239.241822
1   sys1        0   kks2  key1    110.1  ...  title1  300.15   870.35  0.878609 
239.241822
2   sys2        1   kks7  key1    160.1  ...  title1  300.15   870.35  0.878609 
375.821359
3   sys2        1   kks8  key1    170.1  ...  title1  300.15   870.35  0.878609 
375.821359
4   sys2        2  kks13  key1    220.1  ...  title1  300.15   870.35  0.878609 
512.400896
5   sys2        2  kks14  key1    230.1  ...  title1  300.15   870.35  0.878609 
512.400896
6   sys1        0   kks3  key2    120.1  ...  title2  350.85  1750.59  1.035362 
241.654619
7   sys1        0   kks4  key2    130.1  ...  title2  350.85  1750.59  1.035362 
241.654619
8   sys2        1   kks9  key2    180.1  ...  title2  350.85  1750.59  1.035362 
173.948829
9   sys2        2  kks12  key2    210.1  ...  title2  350.85  1750.59  1.035362 
202.924203
10  sys3        0  kks15  key2    240.1  ...  title2  350.85  1750.59  1.035362 
473.457611
11  sys3        0  kks16  key2    250.1  ...  title2  350.85  1750.59  1.035362 
473.457611
12  sys3        1  kks18  key2    270.1  ...  title2  350.85  1750.59  1.035362 
260.874951
13  sys3        2  kks20  key2    290.1  ...  title2  350.85  1750.59  1.035362 
280.191867
14  sys1        0   kks5  key3    140.1  ...  title3  400.47  1830.80  1.000000 
290.200000
15  sys1        0   kks6  key3    150.1  ...  title3  400.47  1830.80  1.000000 
290.200000
16  sys2        1  kks10  key3    190.1  ...  title3  400.47  1830.80  1.000000 
390.200000
17  sys2        1  kks11  key3    200.1  ...  title3  400.47  1830.80  1.000000 
390.200000
18  sys3        0  kks17  key3    260.1  ...  title3  400.47  1830.80  1.000000 
260.100000
19  sys3        1  kks19  key3    280.1  ...  title3  400.47  1830.80  1.000000 
280.100000
20  sys3        2  kks21  key3    300.1  ...  title3  400.47  1830.80  1.000000 
610.200000
21  sys3        2  kks22  key3    310.1  ...  title3  400.47  1830.80  1.000000 
610.200000

[22 rows x 11 columns]

Скорость работы apply в Pandas

#python #pandas #оптимизация #dataframe #group_by

Имеется dataframe из двух столбцов.
Группируя по первому, суммирую значения по втором. Делаю двумя способами

df.groupby('col1')['col2'].sum()


Скорость выполнения: 0,01 сек

df.groupby('col1')['col2'].apply(lambda x: x.sum())


Скорость выполнения: 4,15 сек

Почему такая разница в скорости? 
Как ускорить работу, если нужна будет не просто сумма, а более сложная функция?
Например, 

df.groupby('col1')['col2'].apply(lambda x: ','.join([str(i) for i in x]))

    

Ответы

Ответ 1

Series.apply() и DataFrame.apply(...) - являются "не совсем векторизированными" функциями,
которые чаще всего значительно медленнее своих векторизированных аналогов.

К сожалению, серебрянной пули универсального и быстрого решения не существует.

Так что подход обычно следующий:


если есть вектроизированная функция для решения нашей конкретной задачи, то используем ее
сравниваем скорость работы .apply() и обычного list comprehension и выбираем самый
быстрый вариант. NOTE: при работе со строками (object dtype) list comprehension часто
оказывается быстрее .apply() и иногда быстрее соответствующих векторизированных Series.str.
методов
если скорость не устраивает то пробуем один из подходов, рекоммендованных разработчиками
Pandas:


Cython
Numba
pd.eval()

GROUP BY ORACLE SQL. ORA-00979

#oracle #group_by #sql

Уважаемые коллеги, прошу подсказать ЧЯДНТ? Почему 


  ORA-00979: выражение не является выражением GROUP BY?


Запрос: 

select cp.name as bank_name,
       case 
         when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
              and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
         then count(p.guid) 
       end as "2014",
       case
         when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
              and to_date('20.03.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
         then count(p.guid) 
       end as "2015"
from   cp_providers cp
left   join payments p 
on     cp.guid = p.cpp_guid
where  p.payment_date = '01.01.70'
and    p.is_active = 1 
group  by cp.name;


Большое спасибо!
    

Ответы

Ответ 1

Видимо, ошибка в использовании case и функции группировки count.
Как вариант, можно сделать так:

select cp.name as bank_name,
       COUNT(case
               when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                    and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
               then p.guid
             else NULL 
       end) as "2014",
       COUNT(case
               when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                    and to_date('20.03.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
               then p.guid
            else NULL 
       end) as "2015"
from   cp_providers cp
left   join payments p 
on     cp.guid = p.cpp_guid
where  p.payment_date = '01.01.70'
and    p.is_active = 1 
group  by cp.name;


Ответ 2

Попробуйте так:

select bank_name, 
       sum("2014") as "2014", 
       sum("2015") as "2015"
from   (select cp.name as bank_name,
               case 
                 when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                  and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
                 then 1
                 else 0
               end as "2014",
               case
                 when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                  and to_date('20.03.2015 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
                 then 1
                 else 0
               end as "2015"
        from   cp_providers cp
        left   join payments p 
        on     cp.guid = p.cpp_guid
        where  p.payment_date = '01.01.70'
        and    p.is_active = 1)
 group  by bank_name;


P.S. У Вас ошибка в дате - '20.03.2014 23.59.59', как я понимаю, там должно быть 2015


Ответ 3

Как вариант:

select cp.name, 
       sum(p.c2014) as "2014", 
       sum(p.c2015) as "2015"
from   cp_providers cp 
join   (
       select cpp_guid, 
              count(*) as c2014, 
              0 as c2015
       from   payments 
       where  payment_date = '01.01.70'
       and    is_active = 1
       and    register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy hh24:mi:ss')
       and    to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
       group  by cpp_guid    
       union  all
       select cpp_guid, 
              0, 
              count(*)
       from   payments
       where  payment_date = '01.01.70'
       and    is_active = 1
       and    register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy hh24:mi:ss')
       and    to_date('31.12.2015 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
       group  by cpp_giud
       ) p 
on    cp.guid = p.cpp_guid
group by cp.name

Получение определенной строки среди результатов группировки

#postgresql #group_by

Необходимый результат от SQL-запроса:


Сгруппировать строки
Выделить одну из строк внутри каждой группы (например, строку с минимальным ID)
Вернуть для выбранных строк некоторые поля, не участвующие в фильтрации в предыдущем
пункте


Например, для таблицы users ( id, birthdate, department_id, ... ) получить ID самого
младшего сотрудника в каждом отделе.
В доках PostgreSQL по аггрегирующим функциям похожую проблему решают при помощи вложенных
запросов. Для приведенного выше примера выйдет что-то такое:  

select department_id, min(id) as youngest_user_id
from users users_outer
where birthdate = (
  select max(birthdate)
  from users users_inner
  where users_inner.department_id = users_outer.department_id
)
group by department_id


Вопрос: можно ли достичь того же результата, но без использования подзапросов?
    

Ответы

Ответ 1

Вообще без использования подзапросов достичь того же результата нельзя. Ведь над
данными действительно нужно проделать две различные операции, которые никак не могут
быть сведены к одной, пусть и более сложной, поскольку вторая операция (выбор записи
с максимальной birthdate) даже для того, чтобы просто начать выполняться для какого-то
конкретного department_id, уже требует полного завершения первой операции (группировки
всех данных по department_id).

Довольно легко себе представить алгоритм, посредством которого можно было бы решить
данную задачу за один проход. Но это был бы императивный алгоритм, предполагающий сложную
работу с данными, непрерывно меняющими своё состояние. На SQL такой алгоритм выразить
невозможно, поскольку это декларативный язык запросов, позволяющий описывать лишь желаемый
результат.

Тем не менее, в PostgreSQL есть фича, специально предназначенная как раз для таких
трюков - Window Functions. Эта фича позволяет описывать так называемые окна, которые
по сути представляют собой группы записей без обязательного схлопывания каждой получившейся
группы в единственную запись. Исходные записи не заменяются результатом групповой агрегации,
а просто дополняются одним или несколькими столбцами, которые содержат результат той
или иной агрегирующей функции.

Например, можно пронумеровать строки внутри каждого окна, а во внешнем запросе выбрать
только первые по счету записи:

select department_id, id as youngest_user_id
from (
    select *, 
        row_number() over (partition by department_id order by birthdate desc) as num
    from users
) as s
where num = 1

Группирование по N строк и суммирование

#python #pandas #dataframe #group_by

Имеется Excel с данными: ID Period (время) и значение к примеру D.

Tаблицу отсортировал как нужно, вот код:

import pandas as pd

data = pd.read_excel("C:\\...\\primer.xlsx")

table = pd.pivot_table (data, index="period", columns="id", fill_value=0)


Далее хочу выбрать period он в строку идет, там 1000+ строк, хочу разделить его к
примеру на 5 или 10: 

table["period"] = int[(table["period"]/5)*5]

print(table)


И тут ничего не выходит, вываливается ошибка о том, что не понимает, что такое Period.

Необходимо 1000+ строк разделить на 5, причем просуммировать значение с 0 по 5, с
5 по 10 и т.д. (просуммировать каждые 5 строк по порядку), ну и в конце вывести такую
же матрицу, только в 5 раз меньше по строкам, столбцы остаются как есть. Надеюсь понятно
объяснил.

Пример водных данных. 
Цветом выделил те 5 строк, которые нужно просуммировать и в итоге получить вместо
25 строк, только 5 строк.


    

Ответы

Ответ 1

Пример:

In [5]: df = pd.DataFrame(np.arange(20*3).reshape(-1, 3), columns=list("abc"))

In [6]: df
Out[6]:
     a   b   c
0    0   1   2
1    3   4   5
2    6   7   8
3    9  10  11
4   12  13  14
5   15  16  17
6   18  19  20
7   21  22  23
8   24  25  26
9   27  28  29
10  30  31  32
11  33  34  35
12  36  37  38
13  39  40  41
14  42  43  44
15  45  46  47
16  48  49  50
17  51  52  53
18  54  55  56
19  57  58  59


группируем по три строки:

In [7]: res = df.groupby(np.arange(len(df)) // 3).sum()


результат:

In [8]: res
Out[8]:
     a    b    c
0    9   12   15
1   36   39   42
2   63   66   69
3   90   93   96
4  117  120  123
5  144  147  150
6  111  113  115

Помогите пожалуйста с запросом SQL (Oracle). Having, Group by, агрегатная функция от агрегатной функции

Задание - вывести фамилию-имя начальника отдела и название отдела, в котором сотрудники выполнили максимальное количество проектов. У меня получился такой запрос, но выводит он не совсем то, что нужно. Сделал его по примеру отсюда (второй пункт).

SELECT department_name, max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount
Вывод, при том, что проекта всего три:
В чем проблема - я думаю, вы поймёте, если посмотрете на схему БД. Каждому отделу должно соответствовать несколько проектов, но отделы и проекты связаны через таблицу сотрудников - и поэтому в моём запросе каждому отделу соответствует больше записей, чем нужно. Группирую по названию отдела, а получается, что в каждой группе - сотрудники этого отдела. Как связать таблицы иначе или сгруппировать иначе - я не придумал, в ступоре нахожусь. Если зайти с другой стороны, сгруппировать по id проекта - в каждой группе будет количество сотрудников, которое работало над проектом, тоже не то. Нужно решение задачи именно по этой схеме. Я знаю, что можно упростить, но задание учебное. Скрипт создания БД: http://pastebin.com/hnHnENpX

Заранее спасибо.

Ответ

SELECT emp_first_name,emp_last_name,department_name, max_amount FROM ( SELECT dep_name AS department_name,e2.emp_first_name,e2.emp_last_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees e1 INNER JOIN departments ON e1.emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = e1.emp_id INNER JOIN employees e2 ON e2.emp_id=dep_manager_id GROUP BY dep_name,e2.emp_first_name,e2.emp_last_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount
Хотя верхний подзапрос немного странно смотрится, логичнее бы выглядело, если бы отделы были первой таблицей и к ней все клеилось. Хотя в данном случае порядок ни на что не влияет (т.к. в inner join таблицы слева и справа равнозначны).

Оптимизация SELECT со статистикой

Есть такой запрос:
SELECT COUNT(`count`) AS 'visits', `code` FROM `om_log` WHERE `code` <> '0' AND `date` >='1464728400' AND `date` <='1467320399' GROUP BY `code` ORDER BY `code`;
В таблице записей очень много, это статистика посещения сайта. Обычно она собирается за месяц. Есть идеи как оптимизировать данный запрос, не прибегая к рефакторингу и использованию промежуточных таблиц с счетчиками?
EXPLAIN:
id | select_type | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | range | "code,date" | date | 5 | NULL | 1420 | "Using where; Using temporary; Using filesort"

Ответ

Данную оптимизацию может зделать MySQL вместо меня с помощью механизма партиционирования. Это физическое разделение файла таблицы на несколько по определенному признаку. Таким образом запросы начинают выполнятся в разы быстрее. Главное не переусердствовать с их количеством.
ALTER TABLE `om_log` PARTITION BY RANGE(date) PARTITIONS 6( PARTITION less2015 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01')), PARTITION less2016 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01')), PARTITION less2017 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01')), PARTITION less2018 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01')), PARTITION less2019 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01')), PARTITION other VALUES LESS THAN (MAXVALUE) );
Сейчас я сделал их с запасом, но похорошому не плохо добавлять партиции кроном при необходимости, например в конце года, а партицию 5летней давности допустим удалять.
Сделать партиционирование существующей таблицы, которая активно используется невозможно, возникает блокировка таблицы и это ложит сайт. Необходимо создавать новую таблицу, на которую переключать работу сайта, а после переносить небольшими порциями все записи туда.
Сейчас решена задача добавлениям комбинированных индексов! Скорость запросов 2-5 сек. Это более менее приемлемое время вместо бывших 90сек.

Получение строки значений из groupby

Есть DataFrameGroupby со следующими данными:
last vol datetime 2013-07-23 10:00:00 112450 49 2013-07-23 10:00:00 112440 67 2013-07-23 10:00:00 112430 93 2013-07-23 10:00:00 112420 52 2013-07-23 10:00:00 112410 63
last vol datetime 2013-07-23 10:01:00 112690 17 2013-07-23 10:01:00 112680 59 2013-07-23 10:01:00 112670 226 2013-07-23 10:01:00 112660 184 2013-07-23 10:01:00 112650 289
Сгруппированные по уровню индекса:
blocks_group = datetime_group.groupby(level=0)
Как получить целую строку из каждой группы с максимальным значением, а не только значения столбца vol?

Ответ

Исходный DataFrame:
In [47]: df Out[47]: last vol datetime 2018-08-31 10:00:00 112450 49 2018-08-31 10:00:00 112440 67 2018-08-31 10:00:00 112430 93 2018-08-31 10:00:00 112420 52 2018-08-31 10:00:00 112410 63 2018-08-31 10:01:00 112690 17 2018-08-31 10:01:00 112680 59 2018-08-31 10:01:00 112670 226 2018-08-31 10:01:00 112660 184 2018-08-31 10:01:00 112650 289
Решение:
In [48]: df.groupby(level=0, as_index=False).apply(lambda x: x.nlargest(1, 'vol')) Out[48]: last vol datetime 0 2018-08-31 10:00:00 112430 93 1 2018-08-31 10:01:00 112650 289
Ещё один, менее идиоматичный, вариант:
In [51]: df.sort_values('vol').groupby(level=0).tail(1) Out[51]: last vol datetime 2018-08-31 10:00:00 112430 93 2018-08-31 10:01:00 112650 289

Столбец по сгруппированным данным Pandas

Имеется Pandas DataFrame, например:
city col1 0 nsk 15 1 nsk 17 2 nsk 22 3 vdk 11 4 vdk 9
Требуется добавить столбец, в котором будут суммы значений col1, сгруппированные по столбцу city. Например:
city col1 col2 0 nsk 15 54 1 nsk 17 54 2 nsk 22 54 3 vdk 11 20 4 vdk 9 20

Ответ

In [434]: df['col2'] = df.groupby('city')['col1'].transform('sum')
In [435]: df Out[435]: city col1 col2 0 nsk 15 54 1 nsk 17 54 2 nsk 22 54 3 vdk 11 20 4 vdk 9 20

GROUP BY ORACLE SQL. ORA-00979

Уважаемые коллеги, прошу подсказать ЧЯДНТ? Почему
ORA-00979: выражение не является выражением GROUP BY?
Запрос:
select cp.name as bank_name, case when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy hh24:mi:ss') and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss') then count(p.guid) end as "2014", case when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy hh24:mi:ss') and to_date('20.03.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss') then count(p.guid) end as "2015" from cp_providers cp left join payments p on cp.guid = p.cpp_guid where p.payment_date = '01.01.70' and p.is_active = 1 group by cp.name;
Большое спасибо!

Ответ

Видимо, ошибка в использовании case и функции группировки count. Как вариант, можно сделать так:
select cp.name as bank_name, COUNT(case when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy hh24:mi:ss') and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss') then p.guid else NULL end) as "2014", COUNT(case when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy hh24:mi:ss') and to_date('20.03.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss') then p.guid else NULL end) as "2015" from cp_providers cp left join payments p on cp.guid = p.cpp_guid where p.payment_date = '01.01.70' and p.is_active = 1 group by cp.name;