查询发了5个以上优质产品的会员数量
SQL> SELECT/*+ parallel(a 4)*/ DISTINCT
2 count(a.Admin_Member_Id) over() AS admin_member_cnt
3 FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
4 WHERE A.IS_RIGHT_PROD = 'Y'
5 GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;
ADMIN_MEMBER_CNT
----------------
4902
剖析这条SQL语句
第一步
SQL> SELECT/*+ parallel(a 4)*/
2 a.Admin_Member_Id AS admin_member_cnt
3 FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
4 WHERE A.IS_RIGHT_PROD = 'Y'
5 GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;--按admin_member_id进行分组,查询出产品数>5的所有会员id
ADMIN_MEMBER_CNT
--------------------------------
cnkding
worldfone
hkthu
ecmonster
cnsunprince
cnwisecorp
vstarchina
第二步
SQL> SELECT/*+ parallel(a 4)*/
2 count(a.Admin_Member_Id) over() AS admin_member_cnt
3 FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
4 WHERE A.IS_RIGHT_PROD = 'Y'
5 GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;--count() over()分析函数统计admin_member_id的数量。分析函数不会改变数据量,且分析函数是最后执行的。
ADMIN_MEMBER_CNT
----------------
4902
4902
4902
4902
4902
4902
4902
第三步
SQL> SELECT/*+ parallel(a 4)*/ DISTINCT
2 count(a.Admin_Member_Id) over() AS admin_member_cnt
3 FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
4 WHERE A.IS_RIGHT_PROD = 'Y'
5 GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;--然后再去重
ADMIN_MEMBER_CNT
----------------
4902