Как сопоставить строки таблицы со столбцами с помощью выражений SQL PIVOT или CASE

Вступление

Читая замечательную SQL-антипаттерны книгу Билла Карвина, которая является отличным справочником для любого разработчика, которому необходимо взаимодействовать с системой реляционных баз данных, я нашел пример, в котором предложение SQL PIVOT будет работать как заклинание.

В этом посте я собираюсь объяснить, как транспонировать Результирующий набор с помощью PIVOT, чтобы строки превратились в столбцы.

Модель предметной области

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

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

Схема базы данных метаданных выглядит следующим образом:

Как вы можете видеть, в таблице Свойство используется модель EAV при использовании внешних ключей для ссылок Службы и Компонента .

Проектирование свойств компонентов

Способ запроса данного свойства компонента в нескольких службах заключается в выполнении следующего SQL-запроса:

List componentProperties = entityManager
.createNativeQuery(
    "SELECT " +
    "   p.service_name AS serviceName, " +
    "   p.component_name AS componentName, " +
    "   p.property_name, " +
    "   p.property_value " +
    "FROM Property p " +
    "WHERE " +
    "   p.component_name = :name")
.setParameter("name", "dataSource")
.getResultList();

Однако Результирующий набор выглядит следующим образом:

componentProperties = {java.util.ArrayList@4968}  size = 8
 0 = {java.lang.Object[4]@4971} 
  0 = "Apollo"
  1 = "dataSource"
  2 = "databaseName"
  3 = "high_performance_java_persistence"
 1 = {java.lang.Object[4]@4972} 
  0 = "Artemis"
  1 = "dataSource"
  2 = "databaseName"
  3 = "high_performance_java_persistence"
 2 = {java.lang.Object[4]@4973} 
  0 = "Apollo"
  1 = "dataSource"
  2 = "password"
  3 = "admin"
 3 = {java.lang.Object[4]@4974} 
  0 = "Artemis"
  1 = "dataSource"
  2 = "password"
  3 = "admin"
 4 = {java.lang.Object[4]@4975} 
  0 = "Apollo"
  1 = "dataSource"
  2 = "serverName"
  3 = "192.168.0.5"
 5 = {java.lang.Object[4]@4976} 
  0 = "Artemis"
  1 = "dataSource"
  2 = "url"
  3 = "jdbc:oracle:thin:@192.169.0.6:1521/hpjp"
 6 = {java.lang.Object[4]@4977} 
  0 = "Apollo"
  1 = "dataSource"
  2 = "username"
  3 = "postgres"
 7 = {java.lang.Object[4]@4978} 
  0 = "Artemis"
  1 = "dataSource"
  2 = "username"
  3 = "oracle"

Мы не хотим иметь дело с массивом Object [] , и мы предпочли бы использовать конфигурацию источника данных ДАННЫХ для хранения всей Информации об источнике данных , связанной с данной службой.

Если sql - запрос является строковой переменной, то мы можем сопоставить его Результирующий набор с конфигурацией источника данных ТАКИМ образом:

List dataSources = entityManager
.createNativeQuery(sqlQuery)
.setParameter("name", "dataSource")
.unwrap(Query.class)
.setResultTransformer(
    Transformers.aliasToBean(
        DataSourceConfiguration.class
    )
)
.getResultList();

Вы можете найти более подробную информацию о получении проекций DTO с помощью JPA и гибернации в этой статье .

Вопрос Билла Карвина

В своей книге Билл Карвин предлагает следующий SQL-запрос для переноса Результирующего набора строк в столбцы:

SELECT DISTINCT 
   userName.service_name AS "serviceName", 
   c.name AS "componentName", 
   databaseName.property_value AS "databaseName", 
   url.property_value AS "url", 
   serverName.property_value AS "serverName", 
   userName.property_value AS "userName", 
   password.property_value AS "password" 
FROM Component c 
LEFT JOIN Property databaseName 
   ON databaseName.component_name = c.name AND 
      databaseName.property_name = 'databaseName' 
LEFT JOIN Property url 
   ON url.component_name = c.name AND 
      url.property_name = 'url' 
LEFT JOIN Property serverName 
   ON serverName.component_name = c.name AND 
      serverName.property_name = 'serverName' 
LEFT JOIN Property userName 
   ON userName.component_name = c.name AND 
      userName.property_name = 'username' 
LEFT JOIN Property password 
   ON password.component_name = c.name AND 
      password.property_name = 'password' 
WHERE 
   c.name = :name

Ну, во-первых, этот запрос на самом деле не дает ожидаемого результата, потому что свойства смешиваются между различными службами:

dataSources = {java.util.ArrayList@4990}  size = 2
 0 = {com.vladmihalcea.book.hpjp.hibernate.query.pivot.DataSourceConfiguration@4991} 
  serviceName = "Apollo"
  componentName = "dataSource"
  databaseName = "high_performance_java_persistence"
  url = "jdbc:oracle:thin:@192.169.0.6:1521/hpjp"
  serverName = "192.168.0.5"
  userName = "postgres"
  password = "admin"
 1 = {com.vladmihalcea.book.hpjp.hibernate.query.pivot.DataSourceConfiguration@4994} 
  serviceName = "Artemis"
  componentName = "dataSource"
  databaseName = "high_performance_java_persistence"
  url = "jdbc:oracle:thin:@192.169.0.6:1521/hpjp"
  serverName = "192.168.0.5"
  userName = "oracle"
  password = "admin"

Во-вторых, хотя ВОЛНОВАЯ модель больше похожа на запах дизайна, чем на Анти-шаблон, вышеупомянутый SQL-запрос, безусловно, является Анти-шаблоном с точки зрения производительности.

Мы можем сделать гораздо лучше, чем это!

SQL PIVOT

Как Oracle, так и SQL Server поддерживают предложение PIVOT SQL, поэтому мы можем переписать предыдущий запрос следующим образом:

SELECT * 
FROM ( 
   SELECT 
       p.service_name AS "serviceName", 
       p.component_name AS "componentName", 
       p.property_name , 
       p.property_value 
   FROM Property p 
   WHERE 
       p.component_name = :name
) 
PIVOT(
   MAX(property_value) 
   FOR property_name IN (
       'databaseName' AS "databaseName", 
       'url' AS "url", 
       'serverName' AS "serverName", 
       'username' AS "userName", 
       'password' AS "password") 
)

На этот раз результат выглядит намного лучше:

dataSources = {java.util.ArrayList@4997}  size = 2
 0 = {com.vladmihalcea.book.hpjp.hibernate.query.pivot.DataSourceConfiguration@4998} 
  serviceName = "Apollo"
  componentName = "dataSource"
  databaseName = "high_performance_java_persistence"
  url = null
  serverName = "192.168.0.5"
  userName = "postgres"
  password = "admin"
 1 = {com.vladmihalcea.book.hpjp.hibernate.query.pivot.DataSourceConfiguration@5041} 
  serviceName = "Artemis"
  componentName = "dataSource"
  databaseName = "high_performance_java_persistence"
  url = "jdbc:oracle:thin:@192.169.0.6:1521/hpjp"
  serverName = null
  userName = "oracle"
  password = "admin"

Только для службы Artemis определено свойство url . Более того, для каждого столбца, который нам нужно перенести из значения строки, нет дополнительного СОЕДИНЕНИЯ.

СЛУЧАЙ SQL

Если вы не используете Oracle или SQL Server, вы все равно можете транспонировать строки в столбцы, используя выражение CASE и предложение GROUP BY:

SELECT 
    p.service_name AS "serviceName", 
    p.component_name AS "componentName", 
    MAX(
        CASE WHEN property_name = 'databaseName' 
        THEN property_value END
    ) AS "databaseName", 
    MAX(
        CASE WHEN property_name = 'url' 
        THEN property_value END) 
    AS "url", 
    MAX(
        CASE WHEN property_name = 'serverName' 
        THEN property_value END
    ) AS "serverName", 
    MAX(
        CASE WHEN property_name = 'username' 
        THEN property_value END
    ) AS "userName", 
    MAX(
        CASE WHEN property_name = 'password' 
        THEN property_value END
    ) AS "password" 
FROM Property p 
WHERE 
   p.component_name = :name 
GROUP BY 
    p.service_name, 
    p.component_name

Этот запрос возвращает следующий результат:

dataSources = {java.util.ArrayList@4992}  size = 2
 0 = {com.vladmihalcea.book.hpjp.hibernate.query.pivot.DataSourceConfiguration@4993} 
  serviceName = "Apollo"
  componentName = "dataSource"
  databaseName = "high_performance_java_persistence"
  url = null
  serverName = "192.168.0.5"
  userName = "postgres"
  password = "admin"
 1 = {com.vladmihalcea.book.hpjp.hibernate.query.pivot.DataSourceConfiguration@5177} 
  serviceName = "Artemis"
  componentName = "dataSource"
  databaseName = "high_performance_java_persistence"
  url = "jdbc:oracle:thin:@192.169.0.6:1521/hpjp"
  serverName = null
  userName = "oracle"
  password = "admin"

Вывод

Сводные таблицы-очень удобная функция при работе с отчетами, и для решения этой проблемы существует несколько подходов. Использование выражений PIVOT или CASE является правильным решением, в то время как подход ОБЪЕДИНЕНИЯ является неоптимальным и может привести к неправильному набору результатов .

Для получения дополнительной информации об этой теме ознакомьтесь с этой статьей Маркуса Винанда .