按每项金融资产分列的不同类别的和值

Question

我的SQLite数据库：

CREATE TABLE Movimentacao
( mydate DATE
, dividend_type VARCHAR(200)
, dividends DECIMAL(19, 2)
, ticker VARCHAR(300)
);

插入一些数据：

INSERT INTO Movimentacao VALUES
  ('2021-09-01', 'Dividend',  140, 'BBAS3 - BANCO DO BRASIL')
, ('2021-09-14', 'Dividend',  14,  'PETR4 - PETROBRAS SA')
, ('2021-09-14', 'Refund',    30,  'PETR4 - PETROBRAS SA')
, ('2021-09-04', 'JCP',       100, 'PETR4 - PETROBRAS SA')
, ('2021-09-03', 'Others',    11,  'VGIP12 - VALORA CRI ÍNDICE DE PREÇO FII')
, ('2021-09-21', 'Others',    22,  'IRDM11 - IRIDIUM FII')
, ('2021-10-07', 'Dividend',  100, 'BBAS3 - BANCO DO BRASIL')
;

我的目标是创建下面的查询(正常工作！)但是我想知道是否有一种更优雅的方法来构建我的代码：

有帮助缩小/改进代码吗？

WITH AddColumns AS (
    SELECT
        substr(ticker, 1, INSTR(ticker, ' ')) AS ticker,

        CASE WHEN dividend_type = 'Dividend'
          THEN sum(dividends)
        END AS 'Dividend',
        
        CASE WHEN dividend_type = 'Refund' 
          THEN sum(dividends)
        END AS 'Refund',
        
        CASE WHEN dividend_type = 'JCP' 
          THEN sum(dividends)
        END AS 'JCP'
        
        FROM Movimentacao
        WHERE dividend_type IN ('Dividend', 'JCP', 'Refund') and mydate > '2021-01-01'
        GROUP BY ticker, dividend_type
), RemoveNullAndSum AS (
            SELECT ticker, sum(coalesce(Dividend,0)) as Dividend, 
                         sum(coalesce(JCP,0)) as JCP,
                         sum(coalesce(Refund,0)) as Refund
                FROM AddColumns
            GROUP BY ticker
), CreateTotalColumn AS (
    SELECT ticker, round(Dividend+Refund,2) as Dividend, 
                 round(JCP,2) as JCP,
                 round(Dividend+JCP+Refund,2) as Total FROM RemoveNullAndSum
)
    SELECT * FROM CreateTotalColumn
    UNION ALL
    SELECT 'TOTAL', round(sum(Dividend),2), round(sum(JCP),2), round(sum(Total),2) 
    FROM CreateTotalColumn

代码链接：https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=5ac6537432906bd88ff3a85a6c3f0959

Answer 1

我从您的代码中更改了一些东西，使其比当前版本更加简化：

• SUM聚合函数与CASE滤波运算位置的倒置
• 在CASE内部，由于要将退款加到每种"dividend_type"，而且股利类型为两种，所以您可以通过筛选出不感兴趣的股息来总结所需的值。
• 将“总”字段的所有不同红利类型的和简化为一个简单的SUM(dividends)，它将得到所有感兴趣的"dividend_types"，并给出过滤条件WHERE dividend_type IN ('Dividend', 'JCP', 'Refund')。
• 使用没有分区的聚合直接从中间结果计算汇总。

WITH cte AS (
    SELECT SUBSTR(ticker, 1, INSTR(ticker, ' '))     AS ticker,
           SUM(CASE WHEN dividend_type <> 'JCP'
                    THEN dividends ELSE 0 END)       AS Dividend,
           SUM(CASE WHEN dividend_type <> 'Dividend'
                    THEN dividends ELSE 0 END)       AS JCP,
           SUM(dividends)                            AS Total
    FROM Movimentacao
    WHERE dividend_type IN ('Dividend', 'JCP', 'Refund') AND mydate > '2021-01-01'
    GROUP BY ticker
)
SELECT * FROM cte
UNION ALL
SELECT 'TOTAL', SUM(Dividend), SUM(JCP), SUM(Total) FROM cte

检查演示这里。