一条SQL语句的剖析

查询发了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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值