SQL-запрос для преобразования столбцов в графы строк

У меня есть таблица, которая выглядит следующим образом, которая показывает количество типов. Мне нужно и пытаться отображать данные как 1 столбец и 7 строк, хотя … без успеха.

__________________________________________________________________________ | col types | win2k | winxp | win2k3 | vista | win7 | win8 | win8.1 | -------------------------------------------------------------------------- | count of types | 2365 | 65655 | 422445 | 4822 | 482 | 2331 | 485323 | -------------------------------------------------------------------------- 
 Select count(case when col1 ~* '5.0.2195' then 1 else null end) as Win2k, count(case when col1 ~* '5.1.2600' then 1 else null end) as WinXP, count(case when col1 ~* '5.2.3790' then 1 else null end) as W2k3, count(case when (col1 ~* '6.0.6000' or col1 ~* '6.0.6001' or col1 ~* '6.0.6002') then 1 else null end) as Vista, count(case when (col1 ~* '6.1.7600' or col1 ~* '6.1.7601') then 1 else null end) as Win7, count(case when col1 ~* '6.2.9200' then 1 else null end) as Win8, count(case when (col1 ~* '6.3.9200' or col1 ~* '6.3.9600') then 1 else null end) as "Win8.1" From col1 

В идеале это выглядело бы примерно так:

 ___________________ | types | count | ------------------- | win2k | 2365 | | winxp | 65655 | | win2k3 | 422445 | | vista | 4822 | | win7 | 482 | | win8 | 2331 | | win8.1 | 485323 | ------------------- 

Заметки:

  • Я использую Postgresql 9.3 с PGADMIN III
  • Я не могу создать какие-либо пользовательские функции
  • Не имеет значения, было ли больше столбцов, чтобы сделать эту работу

    Эти типы запросов легче сделать с целью GROUP BY, например:

     Select case when profile.foo ~* '5.0.2195' then 'Win2k' when profile.foo ~* '5.1.2600' then 'WinXP' when profile.foo ~* '5.2.3790' then 'W2k3' when (profile.foo ~* '6.0.6000' or profile.foo ~* '6.0.6001' or profile.foo ~* '6.0.6002') then 'Vista' when (profile.foo ~* '6.1.7600' or profile.foo ~* '6.1.7601') then 'Win7' when profile.foo ~* '6.2.9200' then 'Win8' when (profile.foo ~* '6.3.9200' or profile.foo ~* '6.3.9600') then 'Win8.1' ELSE 'Other' END as type, count(*) as cnt From profile GROUP BY 1 

    Как указано ниже, этот запрос будет работать для взаимоисключающих случаев, то есть когда profile.foo содержит значение, представляющее одну ОС для каждой строки

    Вместо использования условных агрегатов просто используйте CASE чтобы заполнить Type соответствующим образом, затем группу на Type :

      ;with cte AS (Select case when profile.foo ~* '5.0.2195' then 'Win2k' when profile.foo ~* '5.1.2600' then 'WinXP' when profile.foo ~* '5.2.3790' then 'W2k3' when profile.foo ~* '6.0.6000' or profile.foo ~* '6.0.6001' or profile.foo ~* '6.0.6002' then 'Vista' when (profile.foo ~* '6.1.7600' or profile.foo ~* '6.1.7601') then 'Win7' when profile.foo ~* '6.2.9200' then 'Win8' when (profile.foo ~* '6.3.9200' or profile.foo ~* '6.3.9600') then 'Win8.1' end as Type From profile.foo) SELECT Type,COUNT(*) AS ct FROM cte GROUP BY Type 

    Не 100% для синтаксиса postgresql, но логика совместима.

    Мне нравится использовать Postgres specific parallel unnest() для этого:

     SELECT unnest('{win2k,winxp,win2k3,vista,win7,win8,win8.1}'::text[]) AS type ,unnest(ARRAY[ count(some_column ~ '5.0.2195' OR NULL) ,count(some_column ~ '5.1.2600' OR NULL) .. the rest from your query above ... ]) AS ct FROM profile.foo 

    Последовательность и количество значений в обоих массивах должны совпадать.
    Похожие ответы с более подробной информацией (обязательно прочитайте!):

    • Параллельный unsest () и порядок сортировки в PostgreSQL

    • Есть ли что-то вроде функции zip () в PostgreSQL, которая объединяет два массива?

    Альтернативный метод подсчета (необязательно)

    count() учитывает только ненулевые значения.

     (TRUE OR NULL) IS TRUE (FALSE OR NULL) IS NULL (NULL OR NULL) IS NULL 

    Вуаля. TRUE только TRUE . Подробнее в этом ответе на dba.SE.

    Помимо этого: использование ~ вместо ~* в выражениях, так как в этих строковых литералах буквы, чувствительные к регистру, отсутствуют. Но я сомневаюсь, что вам нужно регулярное выражение.

    Кроме того, имена столбцов profile.foo не имеют смысла, поскольку единственная таблица называется foo , а не profile