oracle:用户购买平台案例分析与优化_oracle如何按照用户来买-CSDN博客

本文链接：https://blog.csdn.net/qq_40584718/article/details/105111722

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

文章目录

问题分析

问题及要求

要求:写一段 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;

tmp2
第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;