sql - with - window関数 redshift




SQL over句-パーティションを番号付きサブパーティションに分割 (2)

SQL Server 2012+をお持ちの場合は、lag()とウィンドウ集計を使用してこれを取得できます。

select *,sum(PartNoAdd) over (partition by AccountId order by AsOfDate asc) as PartNo_calc
from
(
    select *,
    case when DebitCredit=lag(DebitCredit,1) over (partition by AccountId order by AsOfDate asc) then 0 else 1 end as PartNoAdd
    from t 
)t2
order by AccountId asc, AsOfDate  asc

内部クエリで、 PartNoAddはこのアカウントの前のDebitCardが同じかどうかを確認します。 もしそうなら、それは0を返します(我々は何も追加するべきではありません)、そうでなければ1を返します。

それから、外側のクエリはこのアカウントのすべてのPartNoAddを合計します。

私は何度も遭遇しましたが、に効率的な解決策を見つけることができなかったという挑戦があります。 たとえば、銀行口座と、借方から貸方への回転の可能性に関するデータを含む大きなテーブルがあるとします。

AccountId DebitCredit AsOfDate
--------- ----------- ----------
aaa       d           2018-11-01
aaa       d           2018-11-02
aaa       c           2018-11-03
aaa       c           2018-11-04
aaa       c           2018-11-05
bbb       d           2018-11-02
ccc       c           2018-11-01
ccc       d           2018-11-02
ccc       d           2018-11-03
ccc       c           2018-11-04
ccc       d           2018-11-05
ccc       c           2018-11-06

上記の例では、DebitCreditがシフトするたびにパーティション番号が増分されるAccountIdとDebitCreditの組み合わせにサブパーティション番号を割り当てます。 言い換えれば、上記の例で私はこの結果が欲しいです:

AccountId DebitCredit AsOfDate   PartNo
--------- ----------- ---------- ------
aaa       d           2018-11-01      1
aaa       d           2018-11-02      1
aaa       c           2018-11-03      2
aaa       c           2018-11-04      2
aaa       c           2018-11-05      2

bbb       d           2018-11-02      1

ccc       c           2018-11-01      1
ccc       d           2018-11-02      2
ccc       d           2018-11-03      2
ccc       c           2018-11-04      3
ccc       d           2018-11-05      4
ccc       c           2018-11-06      5

私は本当にそれを迅速かつ効率的に行う方法を理解することはできません。 操作は何百万もの行を持つテーブルで毎日行われなければなりません。

この例では、すべてのアカウントに対して連続した行があることが保証されています。 しかし、もちろん、顧客はその月の15日に口座を開設したり、26日に口座を閉鎖したりするかもしれません。

問題はMSSQL 2016サーバー上で解決されることですが、2012年(そしておそらく2008r2でさえ)動作する解決策は素晴らしいでしょう。

ご想像のとおり、借方または貸方の行しかないのかどうか、またはアカウントが毎日回転するのかどうかを判断する方法はありません。


あなたは再帰的なCTEでこれを行うことができます

; with
-- the purpose of `cte` is to generate running number in the order of AsOfDate
cte as
(
    select  AccountId, DebitCredit, AsOfDate, rn = row_number() over (partition by AccountId order by AsOfDate)
    from    tbl
),
-- this is the recursive CTE
rcte as
(
    -- anchor member. Starts with `PartNo 1`
    select  AccountId, DebitCredit, AsOfDate, rn, PartNo = 1
    from    cte
    where   rn  = 1

    union all

    -- recursive member. Incrememt `PartNo` if there is a change in debitcredit
    select  c.AccountId, c.DebitCredit, c.AsOfDate, c.rn,
            PartNo = case when r.DebitCredit = c.DebitCredit then r.PartNo else r.PartNo + 1 end
    from    rcte r
            inner join cte c    on  r.AccountId = c.AccountId
                                and r.rn        = c.rn - 1
)
select  *
from    rcte
order by AccountId, AsOfDate




ranking-functions