SQL如何实现层级科目表Header列空值向下填充

我有如下测试数据：

with source (Account,AccountNumber,Indentation) as
(
select 'INCOME STATEMENT',1000,0 union all
select 'REVENUE',1100,0 union all
select 'Revenue - Aircon',1110,1 union all
select 'Revenue - Consumer Goods',1120,1 union all
select 'Revenue - Spares',1130,1 union all
select 'Revenue - Accessories',1140,1 union all
select 'Revenue - Sub Stock',1150,1 union all
select 'Revenue - Services',1160,1 union all
select 'Revenue - Other',1170,1 union all
select 'Revenue - Intercompany',1180,1 union all
select 'Revenue - Delivery Charges',1400,1 union all
select 'COST OF SALES',1500,0 union all
select 'COGS - Aircon',1510,1 union all
select 'COGS - Consumer Goods',1520,1 union all
select 'COGS - Spares',1530,1 union all
select 'COGS - Accessories',1540,1 union all
select 'COGS - Sub Stock',1550,1 union all
select 'COGS - Services',1560,1 union all
select 'COGS - Other',1570,1 union all
select 'COGS - Intercompany',1580,1 union all
select 'COS - Sub Stock Stock Adjustments',1610,1 union all
select 'COS - Sub Stock Repairs',1620,1 union all
select 'COS - Consumables & Packing Materials',1810,1 union all
select 'COS - Freight & Delivery',1820,1 union all
select 'COS - Inventory Adj - Stock Count',1910,1 union all
select 'COS - Inv. Adj - Stock Write up / Write down',1920,1 union all
select 'COS - Provision for Obsolete Stock (IS)',1930,1 union all
select 'COS - Inventory Adj - System A/c',1996,1 union all
select 'COS - Purch & Dir. Cost Appl A/c - System A/c',1997,1 union all
select 'GROSS MARGIN',1999,0 union all
select 'OTHER INCOME',2000,0 union all
select 'Admin Fees Received',2100,1 union all
select 'Bad Debt Recovered',2110,1 union all
select 'Discount Received',2120,1 union all
select 'Dividends Received',2130,1 union all
select 'Fixed Assets - NBV on Disposal',2140,1 union all
select 'Fixed Assets - Proceeds on Disposal',2145,1 union all
select 'Rebates Received',2150,1 union all
select 'Rental Income',2160,1 union all
select 'Sundry Income',2170,1 union all
select 'Warranty Income',2180,1 union all
select 'INTEREST RECEIVED',2200,0 union all
select 'Interest Received - Banks',2210,1
)

select
    Account
,   AccountNumber
,   Indentation
from    source;

我使用如下脚本查询，已经可以将数据拆分为Header、SubHeader1等列：

with s as (
select
    iif(Account like 'Total%',null,iif(Indentation=0,Account,null)) Header
,   iif(Account like 'Total%',null,iif(Indentation=1,Account,null)) SubHeader1
,   *
from    Source
)

select
    Header
,   SubHeader1
,   AccountNumber
,   Indentation
from    s

现在需要对Header列的空值进行向下填充，使每一行都显示对应所属的一级科目名称，我尝试使用LAG()函数实现该需求但没有成功，请问应该如何编写SQL脚本实现该效果？

LAG函数只能取上一行的值，无法直接实现跨多行的向下填充，你可以用「分组标记+窗口聚合」的方案实现，兼容SQL Server、PostgreSQL等支持标准窗口函数的数据库：

with s as (
select
    iif(Account like 'Total%',null,iif(Indentation=0,Account,null)) Header
,   iif(Account like 'Total%',null,iif(Indentation=1,Account,null)) SubHeader1
,   *
from    Source
),
-- 给同属一个一级科目的行打相同的分组标记
t as (
    select *,
        count(Header) over(order by AccountNumber rows between unbounded preceding and current row) as header_grp
    from s
)
select
    -- 取分组内唯一的非空Header值，实现向下填充
    max(Header) over(partition by header_grp) as FilledHeader,
    SubHeader1,
    AccountNumber,
    Indentation
from t

实现逻辑说明 #

• count(Header) 统计时会自动忽略NULL值，每遇到一个非空的一级科目Header，分组计数就会+1
• 同一个分组内的所有行都属于同一个一级科目，用max(Header)取分组内的非空Header值，即可完成空值向下填充

内容的提问来源于stack exchange，提问作者Attie Wagner