Выполняем разворот строк в столбцы в 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: );
