oracle:用户购买平台案例分析与优化

用户购买平台案例,涉及时间型数据.个人第一眼感觉特别简单,但是当深入处理是难成狗了.虽然在测试样例中的结果中通过,但是在最终提交过程中,却显示超时.唉,还得优化呀!本文就是关于这个问题的分析和总结.

---

---

问题分析

问题及要求

要求:写一段 SQL 来查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。

实现思路

从表result中看到platform列中增加了both特征,所以
(1)第一个问题:需要对用户使用的购物平台进行统计,看两者都用还是,只用其中一个.这个问题比较简单,可以采用:

• 分组+count
• select count(*) over()....

(2)表Result在这里插入代码片中每一个日期对应的platform都是3个(mobile,desktop,both),而在Spending表中,7月2日不可能出现both,所以需要考虑将Spending与包含platform重新编码的表右关联;
(3)最后是按照spend_date,platform分组,然后进行特征统计.

数据准备

create table Spending(
	user_id  number(3),
 	spend_date date,
    platform varchar2(10),
    amount number(3));
    
insert into Spending values(1,to_date('2019-07-01','yyyy-mm-dd'),'mobile',100);
insert into Spending values(1,to_date('2019-07-01','yyyy-mm-dd'),'desktop',100);
insert into Spending values(2,to_date('2019-07-01','yyyy-mm-dd'),'mobile',100);
insert into Spending values(2,to_date('2019-07-02','yyyy-mm-dd'),'mobile',100);
insert into Spending values(3,to_date('2019-07-01','yyyy-mm-dd'),'desktop',100);
insert into Spending values(3,to_date('2019-07-02','yyyy-mm-dd'),'desktop',100);

实现

分步实现

(1) 第一步

select s.*,(case when count(distinct(platform)) over(partition by user_id)=2 then 'both'                                     else  platform end) cou from spending s;

结果:

在这里,顺便也进行total_amount的统计,将最终的结果作为第二个问题的基础表(tmp2).

--代码A
select cou,sum(amount) over(partition by cou,spend_date) new_amount
    from (
    select s.*,(case when count(distinct(platform)) over(partition by user_id)=2 then 'both'                                     else  platform end) cou from spending s);

注:实际运行过程中不写,只为了演示方便.

create table tmp2 as A;

第2步:
若将Spending和tmp2表直接关联,结果如下图所示.cou就是platform的新编码,new_amount是amount的统计结果.这种链接方式下无法直接筛选.本文在Spending表中增加新列cou,然后通过其实现连接.(将tmp2中的cou记为cou2)然后修改new_amount记为A,以及有用的user_id 记为new_user_id.

select tmp1.*,tmp2.cou cou2,(case when tmp1.cou=tmp2.cou then new_amount  else 0 end) A ,
        (case when tmp1.cou=tmp2.cou then user_id  else null end) new_user_id
from (
    select s.*,(case when count(distinct(platform)) over(partition by user_id)=2 then 'both'                                     else  platform end) cou from spending s) tmp1,
    (
    select cou,sum(amount) over(partition by cou,spend_date) new_amount
    from (
    select s.*,(case when count(distinct(platform)) over(partition by user_id)=2 then 'both'                                     else  platform end) cou from spending s) )tmp2;
	
	

结果:

(3)基于上述结果实现分组统计

select spend_date,cou2 platform , sum(distinct A) total_amount,count( distinct new_user_id) user_id
from (
select tmp1.*,tmp2.cou cou2,(case when tmp1.cou=tmp2.cou then new_amount  else 0 end) A ,
        (case when tmp1.cou=tmp2.cou then user_id  else null end) new_user_id
from (
    select s.*,(case when count(distinct(platform)) over(partition by user_id)=2 then 'both'                                     else  platform end) cou from spending s) tmp1,
    (
    select cou,sum(amount) over(partition by cou,spend_date) new_amount
    from (
    select s.*,(case when count(distinct(platform)) over(partition by user_id)=2 then 'both'                                     else  platform end) cou from spending s) )tmp2)
group by spend_date,cou2
order by spend_date;

结果和Result相同.

优化

虽然可以实现与Result相同的结果,但是在系统提交时,会出现超时的情况.说明该程序还有优化的空间.从上述分析结果来看,如何快速得到需要分组的数据表时提高程序效率优化的关键.
这个问题先保留,后续解决!

---

该问题的其它数据库上的实现:
1127. 用户购买平台 - 力扣（LeetCode）

---