Блог

Главная - Вопросы по программированию - Запрос с несколькими сводными столбцами, разными именами и агрегированным

Запрос с несколькими сводными столбцами, разными именами и агрегированным

#sql #sql-server #pivot #sql-server-2016

#sql #sql-сервер #сводный #sql-server-2016

Вопрос:

У меня есть следующая таблица CTE под названием assets (пожалуйста, имейте в виду, что количество учетных записей и ISIN исчисляется десятками и сотнями):

  --------------------------- --------- ---------------- --------------------- -------------------- ---------- ---------------------- ------------------ -------- 
|           Date            | Account |      ISIN      |      Quantity       |       Value        | Currency | Price                | PriceCHF         | FXRate |
 --------------------------- --------- ---------------- --------------------- -------------------- ---------- ---------------------- ------------------ -------- 
| 2019-02-28 00:00:00       |       1 | CH5055181629   | 0.00000000          | 0.000000           | CHF      | 1124.56              | 1124.56          | 1      |
| 2019-02-28 00:00:00       |       6 | CH5055181629   | 0.37866100          | 425.827014         | CHF      | 1124.56              | 1124.56          | 1      |
| 2019-02-28 00:00:00       |       7 | CH5055181629   | 0.67151800          | 755.162282         | CHF      | 1124.56              | 1124.56          | 1      |
| 2019-02-28 00:00:00       |       8 | CH5055181629   | 0.45240000          | 508.750944         | CHF      | 1124.56              | 749.7067         | 1.5    |
| 2019-02-28 00:00:00       |       9 | CH5055181622   | 0.02204500          | 24.790925          | CHF      | 1124.56              | 749.7067         | 1.5    |
| 2019-02-28 00:00:00       |      10 | CH5055181622   | 0.08353300          | 93.937870          | CHF      | 1124.56              | 749.7067         | 1.5    |
| 2019-02-28 00:00:00       |      11 | CH5055181622   | 0.89667100          | 1008.360340        | CHF      | 1124.56              | 749.7067         | 1.5    |
 --------------------------- --------- ---------------- --------------------- -------------------- ---------- ---------------------- ------------------ --------

Я хочу динамически изменять столбцы на основе ISIN. В настоящее время у меня есть следующий запрос:

 SELECT * 
INTO   #assets 
FROM   (SELECT xxx 
        FROM   yyyy) a 

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max); 

SET @cols = Stuff((SELECT DISTINCT ','   Quotename(c.ISIN) 
                   FROM   #assets c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 
SET @query = 'SELECT date, account, '   @cols 
               ' from #assets x pivot ( max(quantity) for ISIN in ('   @cols 
               ')) p ' 

EXECUTE(@query) 

DROP TABLE #assets

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

Теперь я хочу, чтобы столбцы были больше, чем просто количество. Я хочу, чтобы имя столбца было, например, ISIN '_quantity' и ISIN '_value' . Кроме того, я хочу, чтобы значения количества были не индивидуально для каждой строки, а агрегированы для Date и Account .

Итак, мой текущий результат следующий:

  --------------------------- --------- -------------- ---------------- 
|           Date            | Account | CH5055181629 | CH5055181622   |
 --------------------------- --------- -------------- ---------------- 
| 2019-02-28 00:00:00       |       1 | 0.000000     | NULL           |
| 2019-02-28 00:00:00       |       6 | 0.378661     | NULL           |
| 2019-02-28 00:00:00       |       7 | 0.671518     | NULL           |
| 2019-02-28 00:00:00       |       8 | 0.452400     | NULL           |
| 2019-02-28 00:00:00       |       9 | 0.000000     | NULL           |
| 2019-02-28 00:00:00       |      10 | 0.000000     | NULL           |
| 2019-02-28 00:00:00       |      11 | 0.000000     | NULL           |
| 2019-02-28 00:00:00       |       1 | NULL         | 0.000000       |
| 2019-02-28 00:00:00       |       6 | NULL         | 0.000000       |
| 2019-02-28 00:00:00       |       7 | NULL         | 0.000000       |
| 2019-02-28 00:00:00       |       8 | NULL         | 0.000000       |
| 2019-02-28 00:00:00       |       9 | NULL         | 0.022045       |
| 2019-02-28 00:00:00       |      10 | NULL         | 0.083533       |
| 2019-02-28 00:00:00       |      11 | NULL         | 0.896671       |
 --------------------------- --------- -------------- ----------------

Теперь мой желаемый результат был бы следующим:

  --------------------------- --------- ----------------------- ----------------------- -------------------- -------------------- 
|           Date            | Account | CH5055181629_quantity | CH5055181622_quantity | CH5055181629_value | CH5055181622_value |
 --------------------------- --------- ----------------------- ----------------------- -------------------- -------------------- 
| 2019-02-28 00:00:00       |       1 | 0.000000              | 0.000000              | 0.000000           | 0.000000           |
| 2019-02-28 00:00:00       |       6 | 0.378661              | 0.000000              | 425.827014         | 0.000000           |
| 2019-02-28 00:00:00       |       7 | 0.671518              | 0.000000              | 755.162282         | 0.000000           |
| 2019-02-28 00:00:00       |       8 | 0.452400              | 0.000000              | 508.750944         | 0.000000           |
| 2019-02-28 00:00:00       |       9 | 0.000000              | 0.022045              | 0.000000           | 24.790925          |
| 2019-02-28 00:00:00       |      10 | 0.000000              | 0.083533              | 0.000000           | 93.937870          |
| 2019-02-28 00:00:00       |      11 | 0.000000              | 0.896671              | 0.000000           | 1'008.360340       |
 --------------------------- --------- ----------------------- ----------------------- -------------------- --------------------

Я застрял в точке, где я хочу иметь два базовых столбца и агрегацию.

Ответ №1:

Может быть, не самое красивое решение, но… Я использую две сводные таблицы (одну для количества и одну для значения) для глобальных временных таблиц, а затем объединяю их:

Таблица:

 Create Table #tbl
(
  Dt Date,
  Account Int,
  ISIN VarChar(20),
  Quantity Float,
  Val Float,
  Currency VarChar(3),
  Price Float,
  PriceCHF Float,
  FXRate Float
)
Insert Into #tbl Values
('2019-02-28 00:00:00', 1,'CH5055181629',0.00000000,0.000000,'CHF',1124.56,1124.56,1),
('2019-02-28 00:00:00', 6,'CH5055181629',0.37866100,425.827014,'CHF',1124.56,1124.56,1),
('2019-02-28 00:00:00', 7,'CH5055181629',0.67151800,755.162282,'CHF',1124.56,1124.56,1),
('2019-02-28 00:00:00', 8,'CH5055181629',0.45240000,508.750944,'CHF',1124.56,749.7067,1.5),
('2019-02-28 00:00:00', 9,'CH5055181622',0.02204500,24.790925,'CHF',1124.56,749.7067,1.5),
('2019-02-28 00:00:00',10,'CH5055181622',0.08353300,93.937870,'CHF',1124.56,749.7067,1.5),
('2019-02-28 00:00:00',11,'CH5055181622',0.89667100,1008.360340,'CHF',1124.56,749.7067,1.5)

Свод 1:

 SELECT * 
INTO   #assets 
FROM   (SELECT * 
        FROM   #tbl) a 

DECLARE @cols1  AS NVARCHAR(max)
DECLARE @cols1q  AS NVARCHAR(max) 
Declare @query AS NVARCHAR(max); 

SET @cols1 = Stuff((SELECT DISTINCT ','   Quotename(c.ISIN) 
                   FROM   #assets c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 

SET @cols1q = Stuff((SELECT DISTINCT ','   'Cast(ISNULL(##pvt1.'   c.ISIN   ',0) As Decimal(15,6)) As '   c.ISIN   '_Quantity'
                   FROM   #assets c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
)
SET @query = 'SELECT dt, account, '   @cols1 
               ' Into ##pvt1 from #assets x pivot ( max(quantity) for ISIN in ('   @cols1 
               ')) p ' 

EXECUTE(@query)

Свод 2:

 SELECT * 
INTO   #assets2 
FROM   (SELECT * 
        FROM   #tbl) a 

DECLARE @cols2  AS NVARCHAR(max) 
DECLARE @cols2v  AS NVARCHAR(max) 
--        ,@query AS NVARCHAR(max); 

SET @cols2 = Stuff((SELECT DISTINCT ','   Quotename(c.ISIN) 
                   FROM   #assets2 c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 

SET @cols2v = Stuff((SELECT DISTINCT ','   'Cast(ISNULL(##pvt2.'   c.ISIN   ',0) As Decimal(15,6)) As '   c.ISIN   '_Value' 
                   FROM   #assets2 c 
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 


SET @query = 'SELECT dt, account, '   @cols2 
               ' Into ##pvt2 from #assets x pivot ( sum(val) for ISIN in ('   @cols2 
               ')) p ' 

EXECUTE(@query)

Присоединяйтесь к сводным столбцам:

 Set @query = 

'Select 
       ##pvt1.Dt,
       ##pvt1.Account,'   @cols1q   ', '   @cols2v  

' From ##pvt1 Inner Join
              ##pvt2 On ##pvt1.Account = ##pvt2.Account
Order By ##pvt1.Account'

EXECUTE(@query)

Очистка:

 Drop Table #assets
Drop Table #assets2
Drop Table ##pvt1
Drop Table ##pvt2
Drop Table #tbl

Комментарии:

1. Я немного переписал его, нет необходимости в двух таблицах ресурсов и нет необходимости разделять создание временной таблицы на две. Я переместил все это в один запрос, который выполняет свод 1, свод 2 и объединяет сводные точки. Теперь он по-прежнему пропускает агрегацию значений в строках, т. Е. Он по-прежнему показывает мне слишком много строк и не агрегирует их по «базовому столбцу» (т. Е. По одной строке на дату и учетную запись, а не по одной строке на столбец значения и количества).).