(SQL) SQL (2010 год)

Выполняем разворот строк в столбцы в MS SQL и PostgreSQL

Разворот строк в столбцы - довольно часто возникающая на практике задача. Когда мне пришлось выполнить ее трижды за один день на разных структурах данных - мое терпение лопнуло и решил написать этот топик. У меня получился некий сквозной примерчки - в этом топике я покажу работу с рекордсетом на уровне данных, а в топике Этюды на ASP.NET. Вложенный (nested) Datalist я покажу как отобразить на формах ASP.NET эти рекордсеты.

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

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

Обратите внимание, на точность курсов валют - в этом банке они намеренно поставлены неточные. Для погпужения в вопросы точности округления прочитайте мой топик от 2004-го года - Банковское округление.

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


   1:  CREATE  View [dbo].[CursValut]
   2:      as
   3:      select  
   4:      FromVal.i as FromVal_i, FromVal.Name as FromVal_Name, FromVal.OrderBy as FromVal_OrderBy,
   5:      ToVal.i as ToVal_i, ToVal.Name as ToVal_Name,
   6:      Kurs.i as Kurs_i, Kurs.Kurs as Kurs, Kurs.Date as Kurs_Date
   7:      from PriceType as FromVal join Kurs on FromVal.i=Kurs.FromValuta
   8:      join PriceType as ToVal on Kurs.Valuta=ToVal.i
   9:  GO





Теперь посмотрим более сложный случай разворота. Мне надо было докрутить платежную систему к моему сайту http://arenda.votpusk.ru/. Этот маленький фрагмент для ведения счетов вы видите на рисунке слева. Смысл таблиц понятен из названий: Account - это счета юзеров, Membership и Users - это собтсвенно таблицы юзеров, AccountType - это тип счета пользователя (на момент проектирования я внес туда три типа валюты - Рубль, Яндекс-деньги, WebMoney - понятно что со временем их там может быть и сотня. ActionType - это типы платных действий (на момент проектирования я внес туда два типа "Поднять вверх в общем списке" и "Разместить на входной странице портала". Опять же понятно, что потом тут появятся "Разместить в левом ухе входной странички", "Разместить в подвале второй странички" и так далее до бесконечности. ActionCost - это стоимость каждой такой операции в конкретной валюте. Нужны фирме сейчас Яндекс-деньги - ставим чуть-чуть выгоднее операции по Яндекс-деньгам. AccountLog - журнал операций по счетам и ActionLog - журнал выполненных действий.

Первое же действие по очеловечиванию этой высокореляционной формы - это вьюшка, которая покажет все счета всех юзеров:


   1:  ALTER view [dbo].[AllAccount]
   2:  as
   3:  select Account.*,AccountType.Name,dbo.aspnet_Users.UserName   from Account
   4:  join dbo.AccountType on dbo.Account.ToValueType=dbo.AccountType.i
   5:  join dbo.aspnet_Users on aspnet_Users.UserId=Account.ToUser
   6:  GO

и все открытые счета всех юзеров


   1:  ALTER View [dbo].[AllOpenAccount]
   2:  as
   3:  select dbo.aspnet_Users.UserId, dbo.aspnet_Users.UserName, 
   4:  dbo.AllAccount.Value, dbo.AllAccount.ToValueType,dbo.AllAccount.Name,dbo.AllAccount.SetValueDate
   5:  from dbo.aspnet_Users 
   6:  left join AllAccount on aspnet_Users.UserId=AllAccount.ToUser
   7:  GO



В данном случае вот такой процедурой


   1:  CREATE Procedure [dbo].[AccountIni]
   2:  @ToUser uniqueidentifier,
   3:  @AccountType int
   4:  as
   5:  declare @AccountNumber int
   6:  Insert [Account] (ToUser,Value,ToValueType,SetValueDate)
   7:  select @ToUser,0,@AccountType,GETDATE()
   8:  where not exists (select * from [Account] where ToUser=@ToUser and ToValueType=@AccountType)
   9:  select @AccountNumber=SCOPE_IDENTITY() 
  10:  select [Account].i as AccountNumber, Value, ToValueType,AccountType.Name   from [Account] 
  11:  join dbo.AccountType on [Account].ToValueType=dbo.AccountType.i
  12:  where ToUser=@ToUser and ToValueType=@AccountType

я открыл три тестовых счета одному и тому же тестовому юзеру:




Однако на формах так показать данные нельзя - через полгода тут будет 50 тысяч юзеров в 20-ти валютах - что делать? Разумно было бы развернуть эту табличку и сделать вьюшку - которая покажет по одной строке на юзера - а каждый открытый счет в каждой валюте будет выводится в своем столбце.




Таким образом ровно через минуту проектирования системы - упираемся в типичную задачку разворота строк в столбцы.

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


   1:  select AllRows.UserID, AllRows.UserName, 
   2:  ValType1.value as Value1, ValType1.ToValueType as ValueType1, ValType1.Name as Name1, ValType1.SetValueDate as SetValueDate1,
   3:  ValType2.value as Value2, ValType2.ToValueType as ValueType2, ValType2.Name as Name2, ValType2.SetValueDate as SetValueDate2,
   4:  ValType3.value as Value3, ValType3.ToValueType as ValueType3, ValType3.Name as Name3, ValType3.SetValueDate as SetValueDate3
   5:  from (select distinct UserID,UserName from AllOpenAccount) as AllRows
   6:  left join AllOpenAccount as ValType1 on AllRows.UserID=ValType1.UserID and ValType1.ToValueType=1   
   7:  left join AllOpenAccount as ValType2 on AllRows.UserID=ValType2.UserID and ValType2.ToValueType=2  
   8:  left join AllOpenAccount as ValType3 on AllRows.UserID=ValType3.UserID and ValType3.ToValueType=3 




Вот и весь принцип разворота в MS SQL. Далее надо все это обернуть в динамику Exec(String) - если количество волют будет меняться - чтобы процедуру не переписывать. Или если с количеством валют админы сайта определятся - то можно просто сделать на этом вьюшку без всякой динамики.




Теперь посмотрим на удивительное по красоте решение в PostgreSQL. Здесь тоже есть данные в высокореляционной форме для типичной скучнейшей складской задачки:




Здесь есть список товаров:




Название товаров:




И значения свойств товара:




Понятно что для любых отборов и отображений товаров надо поставить все свойства товаров в одну строку. Посмотрите на изумительное по красоте решение в PostgreSQL.

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


   1:  CREATE OR REPLACE VIEW "GET_СвойстваТовара" AS 
   2:   SELECT "ЗначенияСвойствТовара".totovar, "СвойстваТовара"."Наименование", "ЗначенияСвойствТовара"."Значение"
   3:     FROM "СвойстваТовара"
   4:     JOIN "ЗначенияСвойствТовара" ON "СвойстваТовара"."Ид"::text = "ЗначенияСвойствТовара"."Ид"::text;



И вот оно чудо - вьюшка, полностью развернувшая строки в столбцы:


   1:  CREATE OR REPLACE VIEW "GET_ВсеСвойстваТовара" 
   2:  AS 
   3:  SELECT * FROM crosstab(
   4:  'select * from "GET_СвойстваТовара"'::text, 
   5:  'select distinct "Наименование" from "GET_СвойстваТовара" order by "Наименование"'::text
   6:  ) 
   7:  as
   8:  (
   9:  totovar integer, "Вылет - ET (мм)" character varying, 
  10:  "Индекс нагрузки шины" character varying, 
  11:  "Индекс скорости шины" character varying, 
  12:  "Посадочный диаметр шины (дюйм)" character varying, 
  13:  "Размер диска" character varying, 
  14:  "Размер шины" character varying, 
  15:  "Сверловка" character varying, 
  16:  "Сезонность шины" character varying, 
  17:  "Тип шины" character varying, 
  18:  "Цвет" character varying, 
  19:  "Центрально отверстие - DIA (мм)" character varying, 
  20:  "Шина повышенной проходимости (M+S)" character varying, 
  21:  "Шина усиленная (C)" character varying, 
  22:  "Шипованная шина" character varying
  23:  );



При попытке повторить это чудо - учтите что функция crosstab не устанавливается автоматически при инсталляции. В виндузне для установки надо найти каталог contrib (C:\Program Files (x86)\PostgresPlus\8.4SS\share\contrib) и кликнуть там по tablefunc.sql



Комментарии к этой страничке ( )
ссылка на эту страничку: http://www.vb-net.ru/TurnRowsIntoColumns/index.htm
<На главную>  <В раздел ASP>  <В раздел NET>  <В раздел SQL>  <В раздел Разное>  <Написать автору>  < Поблагодарить>