寫的一個SQL

為給客戶統計機頂盒使用分佈情況所寫的一個SQL語句。雖然技術含量可能不是最高的，但是絕對是最複雜的。要實現這個統計，前提當然還是要對業務有比較深的瞭解。可能有人會質疑語句的效率，經過實際的執行，最快100秒就能出來結果。而且後續的資料出來速度也很快。
主要是前天跟客戶吹了牛逼，說要用一個語句實現統計需求，看來總算牛逼沒破（以下所有語句都是同一個SQL）：
select aa.*,
(case when aa.subnum = 1 and aa.biztype = 1 and aa.workitem_type_id = 2 then aa.account_id end) "一臺機頂盒(基本型)",
(case when aa.subnum = 1 and aa.biztype = 1 and aa.workitem_type_id = 0 then aa.account_id end) "一臺機頂盒(廣播型)",
(case when aa.subnum = 1 and aa.biztype in(2,3) and aa.is_iptv = 0 then aa.account_id end) "一臺機頂盒(增強無互動)",
(case when aa.subnum = 1 and aa.biztype in(2,3) and aa.is_iptv = 1 then aa.account_id end) "一臺機頂盒(增強有互動)",

(case when aa.subnum = 2 and aa.biztype in(2,3) and aa.is_iptv = 1 and aa.tsubwtpid = 2
and (select count(1) from ow_subscriber sub 
where sub.subscriber_id <> aa.subscriber_id
and sub.status not in(6,7,8)
and sub.biztype = 1
and sub.account_id = aa.account_id) = 1 then aa.account_id end) "兩臺機頂盒(增強有互動/基本)",

(case when aa.subnum = 2 and aa.biztype in(2,3) and aa.is_iptv = 0 and aa.tsubwtpid = 2
and (select count(1) from ow_subscriber sub 
where sub.subscriber_id <> aa.subscriber_id
and sub.status not in(6,7,8)
and sub.biztype = 1
and sub.account_id = aa.account_id) = 1 then aa.account_id end) "兩臺機頂盒(增強無互動/基本)",

(case when aa.subnum = 2 and aa.biztype in(2,3) and aa.is_iptv = 1 and aa.tsubwtpid = 0
and (select count(1) from ow_subscriber sub 
where sub.subscriber_id <> aa.subscriber_id
and sub.status not in(6,7,8)
and sub.biztype = 1
and sub.account_id = aa.account_id) = 1 then aa.account_id end) "兩臺機頂盒(增強有互動/廣播)",

(case when aa.subnum = 2 and aa.biztype in(2,3) and aa.is_iptv = 0 and aa.tsubwtpid = 0
and (select count(1) from ow_subscriber sub 
where sub.subscriber_id <> aa.subscriber_id
and sub.status not in(6,7,8)
and sub.biztype = 1
and sub.account_id = aa.account_id) = 1 then aa.account_id end) "兩臺機頂盒(增強無互動/廣播)",

(case when aa.subnum = 2 and aa.biztype in(2,3) and aa.biznum = 2 and aa.iptvnum = 1 then aa.account_id end) "兩臺機頂盒(增強有/無互動)",
(case when aa.subnum = 2 and aa.biztype in(2,3) and aa.biznum = 2 and aa.iptvnum = 2 then aa.account_id end) "兩臺機頂盒(增強都有互動)",

(case when aa.subnum = 2 and aa.biztype = 1 and aa.workitem_type_id = 2 and aa.tsubwtpid = 0
and (select count(1) from ow_subscriber sub 
where sub.subscriber_id <> aa.subscriber_id
and sub.status not in(6,7,8)
and sub.biztype = 1
and sub.account_id = aa.account_id) = 1 then aa.account_id end) "兩臺機頂盒(基本/廣播)",

(case when aa.subnum = 2 then aa.account_id end) "兩臺機頂盒(總數)",
(case when aa.subnum = 3 and aa.biztype in(2,3) and aa.biznum = 1 and aa.is_iptv = 1 then aa.account_id end) "三臺機頂盒(一臺增強有互動)",
(case when aa.subnum = 3 and aa.biztype in(2,3) and aa.biznum = 1 and aa.is_iptv = 0 then aa.account_id end) "三臺機頂盒(一臺增強無互動)",
(case when aa.subnum = 3 and aa.biztype in(2,3) and aa.biznum = 2 and aa.iptvnum = 2 then aa.account_id end) "三臺機頂盒(兩臺增強都有互動)",
(case when aa.subnum = 3 and aa.biztype in(2,3) and aa.biznum = 2 and aa.iptvnum = 1 then aa.account_id end) "三臺機頂盒(兩臺增強有/無互動)",
(case when aa.subnum = 3 then aa.account_id end) "三臺機頂盒(總數)",
(case when aa.subnum >= 4 and aa.biztype in(2,3) and aa.biznum = 1 then aa.account_id end) "四臺機頂盒(一臺增強)",
(case when aa.subnum >= 4 and aa.biztype in(2,3) and aa.biznum >= 2 then aa.account_id end) "四臺機頂盒(兩臺以上增強)",
(case when aa.subnum >= 4 then aa.account_id end) "四臺機頂盒(總數)"
 from (
 select nn.*, sum(is_iptv) over(partition by nn.account_id, nn.biztype) iptvnum,
 lead(nn.workitem_type_id) over(partition by nn.account_id order by nn.biztype desc) tsubwtpid --兩臺機頂盒的情況使用該欄位
  from (
select sub.account_id, rec.biztype,  sub.subscriber_id, pr.workitem_type_id,
count(sub.subscriber_id) over(partition by sub.account_id) subnum,
count(rec.biztype) over(partition by sub.account_id, rec.biztype) biznum,
(case when (select count(1) from ow_subscription spn, ow_product pr
where pr.service_type in(2,3)
and spn.status not in(6,7,8)
and pr.id = spn.product_id
and spn.subscriber_id = ssp.subscriber_id) = 0 then 0 else 1 end) is_iptv
from ow_subscription ssp, ow_subscriber sub, ow_product pr, ow_resource_category rec
where rec.id = pr.resource_category_id
and pr.id = ssp.product_id
and sub.subscriber_id = ssp.subscriber_id
and ssp.subscription_type = 4
--and ssp.account_id = 244063
and ssp.status not in(6,7,8)) nn) aa