Статьи

Вы уже используете SQL PIVOT?

Февраль 19, 2019

Время от времени мы сталкиваемся с этими редкими проблемами SQL, когда мы хотели бы сделать что-то необычное. Одной из таких вещей является поворот строк в столбцы.

Недавний вопрос о переполнении стека Валианте задал именно этот вопрос. Исходя из этой таблицы:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
+------+------------+----------------+-------------------+
| dnId | propNameId |  propertyName  |   propertyValue   |
+------+------------+----------------+-------------------+
|    1 |         10 | objectsid      | S-1-5-32-548      |
|    1 |         19 | _objectclass   | group             |
|    1 |         80 | cn             | Account Operators |
|    1 |         82 | samaccountname | Account Operators |
|    1 |         85 | name           | Account Operators |
|    2 |         10 | objectsid      | S-1-5-32-544      |
|    2 |         19 | _objectclass   | group             |
|    2 |         80 | cn             | Administrators    |
|    2 |         82 | samaccountname | Administrators    |
|    2 |         85 | name           | Administrators    |
|    3 |         10 | objectsid      | S-1-5-32-551      |
|    3 |         19 | _objectclass   | group             |
|    3 |         80 | cn             | Backup Operators  |
|    3 |         82 | samaccountname | Backup Operators  |
|    3 |         85 | name           | Backup Operators  |
+------+------------+----------------+-------------------+

… мы хотели бы преобразовать строки в столбцы как таковые:

1
2
3
4
5
6
7
+------+--------------+--------------+-------------------+-------------------+-------------------+
| dnId |  objectsid   | _objectclass |        cn         |  samaccountname   |       name        |
+------+--------------+--------------+-------------------+-------------------+-------------------+
|    1 | S-1-5-32-548 | group        | Account Operators | Account Operators | Account Operators |
|    2 | S-1-5-32-544 | group        | Administrators    | Administrators    | Administrators    |
|    3 | S-1-5-32-551 | group        | Backup Operators  | Backup Operators  | Backup Operators  |
+------+--------------+--------------+-------------------+-------------------+-------------------+

Идея состоит в том, что нам нужен только один ряд на отдельный dnId , а затем мы хотим преобразовать пары свойство-имя-значение в столбцы, по одному столбцу на имя свойства.

Использование Oracle или SQL Server PIVOT

Вышеуказанное преобразование на самом деле довольно просто с Oracle и SQL Server , которые поддерживают ключевое слово PIVOT в табличных выражениях.

Вот как можно получить желаемый результат с помощью SQL Server:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) AS t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    objectsid,
    _objectclass,
    cn,
    samaccountname,
    name
  )
) AS p;

(SQLFiddle здесь)

И тот же запрос с немного другим синтаксисом в Oracle:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
SELECT p.*
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    'objectsid'      as "objectsid",
    '_objectclass'   as "_objectclass",
    'cn'             as "cn",
    'samaccountname' as "samaccountname",
    'name'           as "name"
  )
) p;

(SQLFiddle здесь)

Как это работает?

Важно понимать, что PIVOT (очень похоже на JOIN ) — это ключевое слово, которое применяется к ссылке на таблицу для ее преобразования. В вышеприведенном примере мы по существу преобразуем производную таблицу t чтобы сформировать сводную таблицу p . Мы могли бы пойти дальше и присоединить p к другой производной таблице следующим образом:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
SELECT *
FROM (
  SELECT dnId, propertyName, propertyValue
  FROM myTable
) t
PIVOT(
  MAX(propertyValue)
  FOR propertyName IN (
    'objectsid'      as "objectsid",
    '_objectclass'   as "_objectclass",
    'cn'             as "cn",
    'samaccountname' as "samaccountname",
    'name'           as "name"
  )
) p
JOIN (
  SELECT dnId, COUNT(*) availableAttributes
  FROM myTable
  GROUP BY dnId
) q USING (dnId);

Вышеупомянутый запрос теперь позволяет находить те строки, для которых нет пары имя / значение в каждом столбце. Давайте предположим, что мы удалили одну из записей из исходной таблицы, приведенный выше запрос может теперь вернуть:

1
2
3
4
5
| DNID |    OBJECTSID | _OBJECTCLASS |                CN |    SAMACCOUNTNAME |              NAME | AVAILABLEATTRIBUTES |
|------|--------------|--------------|-------------------|-------------------|-------------------|---------------------|
|    1 | S-1-5-32-548 |        group | Account Operators | Account Operators | Account Operators |                   5 |
|    2 | S-1-5-32-544 |        group |    Administrators |            (null) |    Administrators |                   4 |
|    3 | S-1-5-32-551 |        group |  Backup Operators |  Backup Operators |  Backup Operators |                   5 |

jOOQ также поддерживает предложение SQL PIVOT через его API .

Что делать, если у меня нет PIVOT?

В простых сценариях PIVOT пользователи других баз данных, кроме Oracle или SQL Server, могут написать эквивалентный запрос, который использует выражения GROUP BY и MAX(CASE ...) как описано здесь в этом ответе .

Ссылка: Вы уже используете SQL PIVOT? Вам следует! от нашего партнера JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ .
31
Share: