为给客户统计机顶盒使用分布情况所写的一个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