精华内容
下载资源
问答
  • MySQL-查询指定时间段数据,近7天、指定季度、年度数据 业务需求需要查询指定时间段数据,特此记录,有更优时则优化更新。 字母缩写 TB 操作表(TableName) q_time 操作的时间字段(query_time) 代码都是经过...

    描述

    业务需求需要查询指定时间段数据,特此记录,有更优时则优化更新。

    • 字母缩写
      • TB 操作表(TableName)
      • q_time 操作的时间字段(query_time)
        业务中测试语句
        代码都是经过Navicat Premium 12实测的,写法不唯一,但是结果是对的。
        Ctrl C + V后可以直接使用则直接套用,但是事后一定要再测试再学习,做好吸纳知识的准备,看一遍就懂也至少测试一下。
        即使是为了下次复制方便一些,至少把网址收藏,或者记下笔记。吾与君共勉之.。
        在这里插入图片描述

    查询指定日期的数据

    今天

    1)SELECT * FROM `TB` WHERE q_time between '2021-02-05 00:00:00' and '2021-02-05 23:59:59' 
    
    // CURDATE() 和 DATE_FORMAT(NOW(),'%Y-%m-%d') 和 '%2021-02-05%' 所获得数据是一样的,选其一。使用日期函数的话就需要使用concat去拼接,直接用%%就会不识别函数。2)SELECT * FROM `TB` WHERE q_timelike concat('%',DATE_FORMAT(NOW(),'%Y-%m-%d'),'%');
         SELECT * FROM `TB` WHERE sdq_timete like '%2021-02-05%';
    
    3)SELECT * from `TB` WHERE DATE(q_time) = CURDATE();
    
    // TO_DAYS() 加上 [加减操作] 也可以获取昨天等指定日期数据。4)SELECT * FROM `TB` WHERE TO_DAYS(q_time) = TO_DAYS(NOW());
    
    5)SELECT * FROM `TB` WHERE DATE_FORMAT( q_time, '%Y%m%d' ) = DATE_FORMAT( CURDATE( ) , '%Y%m%d' );
    
    6)SELECT * FROM `TB` mes_inspection_record WHERE ( datediff ( q_time, '2021-02-06' ) = 0 );
    
    6)SELECT * FROM `TB` WHERE ( datediff ( q_time , '2021-02-06' ) = 0 );
    
    7)SELECT * FROM `TB` WHERE year(q_time) = 2021 and month(q_time)= 02 and day(q_time) = 06;
    

    昨天

    // TO_DAYS() 函数:返回天数,从年份0开始距离你给定时间的天数。其实就像大区间天数减去小区间天数,
    // 重复的天数就是舍去的交集。显而易见查询前天,则差值为 [<= 2] [> 1] 即可。1)SELECT * FROM `TB` WHERE TO_DAYS(NOW()) - TO_DAYS(q_time) <= 1 and TO_DAYS(NOW()) - TO_DAYS(sdate) > 0;
    
    // 与 TO_DAYS() 函数一样,则同理可得查询前天,则差值为 [ -2 ] 即可。下面不再赘述。2)SELECT * FROM `TB` WHERE DATEDIFF(q_time,NOW())=-1;
    

    前天

    SELECT * FROM `TB` WHERE TO_DAYS(NOW()) - TO_DAYS(q_time) <= 2;
    

    查询指定时间段的数据

    查询近X天的我一般都是使用用这个 DATE_SUB() 函数,MySQL自带的日期截取函数。

    本周

    // PS:外国人一周是从礼拜天开始的,我们的结果也是从礼拜天开始~周六。
    SELECT * FROM `TB` WHERE YEARWEEK(date_format(q_time,'%Y-%m-%d')) = YEARWEEK(now());
    

    上周

    // 同理近几周直接更改对应的周数差值即可
    SELECT * FROM `TB` WHERE YEARWEEK(date_format(q_time,'%Y-%m-%d')) = YEARWEEK(now())-1;
    

    近7天

    // 语义是查询TB表中q_time字段从今天日期开始往回倒7天之后的所有数据。
    // PS:往回倒7天之后的所有,会包括今天和未来的数据,做测试的时候要注意。虽然真实业务中一般没有提前添加未来的数据,但是建议只要不能保证数据一定不会有未来日期的,都要做判断(下面fix分组是因为每天有多条数据,随机应变吧)。
    SELECT * FROM `TB` t1 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(q_time);
    fix:
    SELECT * FROM `TB` t1 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(q_time) 
    AND now() > date(q_time) GROUP BY DATE_FORMAT( t1.q_time, '%Y-%m-%d' );
    

    近30天

    // 与查询近7天同理。
    SELECT * FROM `TB` WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(sdate);
    

    近半年(月)

    // 同理近一个季度则[ 3 ],近8个月则[ 8 ]。
    SELECT * FROM `mes_inspection_record` WHERE sdate BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) and NOW();
    
    

    本月

    // 同理本(今)天:'%Y%m%d'。本(今)年:'%Y'。
    SELECT * FROM `TB` WHERE DATE_FORMAT(q_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
    

    上一月

    // 同理上一年:'%Y'。
    SELECT * FROM `TB` WHERE PERIOD_DIFF( date_format( NOW() , '%Y%m' ) , DATE_FORMAT(q_time, '%Y%m' ) ) =1;
    

    本季度

    // 同理上一年:'%Y'。
    SELECT * FROM `TB` WHERE QUARTER(q_time)=QUARTER(NOW());) ) =1;
    

    上一季度

    // 同理上上季度[ =2 ]
    SELECT * FROM `TB` WHERE QUARTER(q_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
    

    本年

    SELECT * FROM `TB` WHERE YEAR(q_time)=YEAR(NOW());
    

    去年

    // 同理前年为[ =2 ]
    SELECT * FROM `TB` WHERE YEAR(q_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 year));
    

    过去7天和未来七天

    select * from `TB` where q_time between DATE_SUB(CURDATE(), INTERVAL 1 WEEK) and DATE_SUB(CURDATE(), INTERVAL -1 WEEK);
    

    环比(去年的今天)

    // 同理前年为[ =2 ]
    SELECT * FROM `TB` WHERE DATE_SUB(CURDATE(), INTERVAL 1 year) = date(q_time)
    

    同比(上月的今天)

    // 同理前年为[ =2 ]
    SELECT * FROM `TB` WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) = date(q_time)
    
    展开全文
  • 某零售行业拥有多家连锁零售门店,现为12月份,我们现在需要调出历史零售数据,做销售表现的对比,复盘分析今年的销售表现,看去年制定的KPI现在达标情况、做同比环比数据表的关系如下图:在hive中创建表:CREATE ...

    86eb6916d704eedf4e2e9a4cf09bc23e.png

    某零售行业拥有多家连锁零售门店,现为12月份,我们现在需要调出历史零售数据,做销售表现的对比,复盘分析今年的销售表现,看去年制定的KPI现在达标情况、做同比环比。

    数据表的关系如下图:

    2e383ff923a414ab3fa41084114fd1e1.png

    在hive中创建表:

    CREATE TABLE tbDate(dateID string,theyearmonth string,theyear string,themonth string,thedate string,theweek string,theweeks string,thequot string,thetenday string,thehalfmonth string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' ;
    
    CREATE TABLE tbStock(ordernumber STRING,locationid string,dateID string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' ;
    
    CREATE TABLE tbStockDetail(ordernumber STRING,rownum int,itemid string,qty int,price int ,amount int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' ;

    导入数据:

    LOAD DATA LOCAL INPATH '/home/yuanyi/tbDate.txt' INTO TABLE tbDate;
    
    LOAD DATA LOCAL INPATH '/home/yuanyi/tbStock.txt' INTO TABLE tbStock;
    
    LOAD DATA LOCAL INPATH '/home/yuanyi/tbStockDetail.txt' INTO TABLE tbStockDetail;

    先把表聚合起来,方便后续使用:

    hive> create table agg_tb_1 as
        > select sd.*,s.locationid,s.dateid,d.theyearmonth,d.theyear,d.themonth,d.thedate,d.theweek
        > from tbstockdetail sd join tbstock s
        > on sd.ordernumber=s.ordernumber
        > join tbdate d
        > on s.dateid=d.dateid;

    显示创建成功后验证:

    select * from agg_tb_1 limit 5;

    能查看到五行记录

    16547b5b19377379a81c2eb440a837fd.png
    hive> select * from agg_tb_1 limit 5;
    --得到287942条数据;

    我们去开始提取和计算这287942条数据:

    这里假设去年老板用历史占比法去制定每个月的KPI。

    历史占比法:

    即根据前一年,或者前几年中的每个维度的销售额对于整体销售额的占比,去计算将来目标该如何分配到每个月。

    举例来说,现在将目标拆分成一个月份维度,如下图第三列是2006年每个月的销售额,第四列是2006年整年销售额,老板希望2007年整体销售额提升20%,即:13682815 x 1.2 = 16419378,按2006年每个月占全年销售额的占比,用总KPI x 该占比分配到2007年每个月的KPI,即第六列next_year_kpi

    482cd0eb91ddfc4fdcd81780ee4beac6.png

    一维分配KPI:

    下面计算上面例子中的一维分配KPI:

    Hive SQL实现代码:

    hive> select c.*,c.year_amount*1.2*c.zhanbi as next_year_kpi 
        > from (select a.*,b.year_amount,round(a.amount/b.year_amount,4) as zhanbi
        > from (select theyearmonth as yearmonth,sum(qty) as qty,sum(amount) as amount
        > from agg_tb_1 where theyear =2006 group by theyearmonth order by theyearmonth)a join (select distinct theyearmonth as yearmonth,theyear as theyear, sum(amount) over (partition by theyear) as year_amount from agg_tb_1 where theyear =2006 order by theyear,theyearmonth)b 
        > on a.yearmonth=b.yearmonth order by a.yearmonth)c;
    

    Hive SQL运行验证结果:

    ff27df6210f1437e390c1dd1c846bc42.png

    现在是2007年年末,已有2007年全年销售数据,要去计算每个月的达标率,这里还是会用到上面代码的结果,并用lag()函数做位置偏移,使2006年算的the_year_kpi均往下移至和2007年对齐,再算达标率:

    预计效果如图:

    7b2bd788e1439219d03fad91821a39f0.png

    Hive SQL实现代码:

    hive> select 
        > d.*,
        > lag(d.next_year_kpi,12,null) over (order by d.yearmonth) as kpi,
        > concat(round(d.amount/(lag(d.next_year_kpi,12,0) over (order by d.yearmonth))*100,2),"%") as achieving_rate
        > from
        > (
        > select c.*,c.year_amount*1.2*c.zhanbi as next_year_kpi
        > from
        > (
        > select a.*,b.year_amount,round(a.amount/b.year_amount,4) as zhanbi
        > from
        > (select theyearmonth as yearmonth,theyear as theyear,sum(qty) as qty,sum(amount) as amount
        > from agg_tb_1
        > where theyear in (2006,2007)
        > group by theyearmonth,theyear
        > order by theyearmonth,theyear
        > )a
        > join 
        > (select 
        > distinct theyear as theyear, 
        > sum(amount) over (partition by theyear) as year_amount
        > from agg_tb_1
        > where theyear in (2006,2007)
        > order by theyear
        > )b
        > on a.theyear=b.theyear
        > order by a.yearmonth
        > )c
        > )d;
    

    Hive SQL运行验证结果:

    63eb97f7ebada2f342307cf872fdbd16.png

    二维分配KPI:

    假如我们希望再细分维度,希望对每个门店的每个月做分配KPI,同样是用历史占比法。

    但要注意的是细分维度后,对于门店,可能有特殊情况,比如说:门店在2006年的某个月才开始开业,或者2007年的某个月或某几个月暂定停业。

    这样会缺失对应的月份和门店数据,lag()偏移多少的参数无法确认。所以这里就要用两个表去链接:

    先创建视图

    hive> create view location_monthly as
        > select a.*,b.year_amount,a.amount/b.year_amount as zhanbi
        > from
        > (select theyearmonth as yearmonth, locationid as location,theyear as theyear,sum(qty) as qty,sum(amount) as amount
        > from agg_tb_1
        > where theyear in (2006,2007)
        > group by theyearmonth,locationid,theyear
        > order by theyearmonth,locationid,theyear)a
        > join
        > (select distinct theyear as theyear, 
        > sum(amount) over (partition by theyear) as year_amount
        > from agg_tb_1
        > where theyear in (2006,2007)
        > order by theyear)b
        > on a.theyear=b.theyear
        > order by a.yearmonth,a.location;
    

    创建成功后,查看2006年整体销售额,并算出x1.2是多少。 因为hql不能select @变量=xxx,所以只能用set,先用select计算出数值,再用set定义。

    这里可以对比一下MySQL和Hive SQL

    Hive SQL:

    hive> set kpi=16419378;
    
    --在hive中用set 变量去查看变量被赋值了什么
    hive> set kpi;
    kpi=16419378
    
    hive> select * from(
        > select 
        > a.*,
        > a.year_amount*1.2 as next_year_kpi,
        > b.zhanbi as last_year_zhanbi,
        > ${hiveconf:kpi}*b.zhanbi as kpi,
        > a.amount/(${hiveconf:kpi}*b.zhanbi) as attach_rate
        > from
        > location_monthly a left join location_monthly b
        > on a.theyear-1=b.theyear
        > and a.location = b.location
        > and substr(a.yearmonth,5,2)=substr(b.yearmonth,5,2))a
        > where kpi is not null;
    

    Hive SQL运行效果:

    08000d454e9ddfc663c96bb8b488b496.png

    MySQL:

    select @kpi:=sum(amount)*1.2 from agg_stock where theyear=2006;
    
    select @kpi;
    --可以看到kpi的值为16419378
    
    select * from(
        select 
            a.*,
            a.year_amount*1.2 as next_year_kpi,
            b.zhanbi as last_year_zhanbi,
            @kpi*b.zhanbi as kpi,
            concat(round(a.amount/(@kpi*b.zhanbi)*100,2),"%") as attach_rate
        from
            location_monthly a 
        left join 
            location_monthly b
        --b表为2007年,-1年为2006年和a表链接
        on a.theyear-1=b.theyear
        --地址相同的链接,月份相同的链接
        and a.location = b.location
        and substr(a.yearmonth,5,2)=substr(b.yearmonth,5,2)
        )c
    --只看2007年有被分配到kpi的数据,也可以在select中选择所需字段
    where kpi is not null;
    

    MySQL在MySQLWorkbeach的效果如图:

    9e16d89b7cbffed21a76c9281caabba5.png

    同时可以用order by location,yearmonth去更方便去看每个门店在每个月的销售额和达标率,或者用where去筛选门店看某个门店的两年对比情况。

    举个例子:

    select * from(
        select 
            a.*,
            a.year_amount*1.2 as next_year_kpi,
            b.zhanbi as last_year_zhanbi,
            @kpi*b.zhanbi as kpi,
            concat(round(a.amount/(@kpi*b.zhanbi)*100,2),"%") as attach_rate
        from
            location_monthly a 
        left join 
            location_monthly b
        --b表为2007年,-1年为2006年和a表链接
        on a.theyear-1=b.theyear
        --地址相同的链接,月份相同的链接
        and a.location = b.location
        and substr(a.yearmonth,5,2)=substr(b.yearmonth,5,2)
        )c
    --只看2007年有被分配到kpi的数据,也可以在select中选择所需字段
    where location = "RM"
    order by location,yearmonth;
    

    这里可以看出RM这家店有可能是2006年6月新开业的,在2006奶奶1月到5月都没有数据,用历史占比发去计算2007年的6月-12月kpi,可以看出该门店的达标率情况不错。

    53d916466913945f9914881c6440771c.png

    计算每个月销售额同比、环比:

    期望达到效果:

    2ad5f6248aef39bd782ba579ef17da4d.png

    先存一个2006年和2007年的表:

    hive> create view cal_diff as
        > select 
        > theyearmonth,
        > sum(qty) as qty,
        > sum(amount) as amount
        > from agg_tb_1
        > where theyear in(2006,2007)
        > group by theyearmonth
        > order by theyearmonth;

    同样可以用lag() 去算同比、环比

    hive> select 
        > *,
        > concat(round((amount-(lag(amount,1,null) over(order by theyearmonth)))/amount*100,2),"%") as huanbi,
        > concat(round((amount-(lag(amount,12,null) over(order by theyearmonth)))/amount*100,2),"%") as tongbi
        > from 
        > cal_diff
        > limit 12,12;

    Hive SQL运行验证结果:

    0a28f922ce3d951d9c3926ff241a15e8.png

    这里我们可以看到,除了一月和三月,其他都同比上升。 2月同比上升幅度最大,超过了50%。

    注意千万不要用where筛选2007年的数据,同比就会显示不出来,因为算不了2006年的结果,如图:

    a34c0a744531a0adee68a1c881be70b7.png
    展开全文
  • 今天跟大家分享一张Excel销售业绩分析报表,自动占比排名,同比环比分析,简单实用。为了让大家能更稳定的下载模板,我们又开通了全新下载方式(见文章末尾),以便大家可以轻松获得免费模板和收费模板。メ大家请看...

    Hello大家好,我是帮帮。今天跟大家分享一张Excel销售业绩分析报表,自动占比排名,同比环比分析,简单实用。

    为了让大家能更稳定的下载模板,我们又开通了全新下载方式(见文章末尾),以便大家可以轻松获得免费模板和收费模板。

    メ大家请看范例图片,销售业绩分析报表(半年度),上方为数据源区域,下方为图表区域。メ

    a4c9a126a17e16bc357ada84e3ecd6c2.png

    メ上半年合计数、占比、排名、同比、环比数据计算,函数自动显示。メ

    806318df4c1b11b5c83718921c0bc5af.png

    メ四张图表多维度展示,自动显示不操心。メ

    0af16f79c2cabe2c6b5bcc92d76d4053.png

    メ点击图表,设计,颜色,一键换色,个性化DIY。メ

    7a8869694e544d7b14e1c186609fca03.png

    メ修改数据源的项目,图表坐标轴自动更新变化。メ

    0a8567cb34cffdb74f8680118c6d6ef3.png

    メ如果要增减数据源,直接复制多行删除,或者插入,快速调整,有需要的小伙伴赶紧GET住。メ

    7d165b426c06bb800e4276f3be73ec5a.png

    模板获取方式

    点击文末的“阅读原文”链接,按照提示回复下载码,即可获取本模版的下载地址

    我们每天会更新1个免费office教程,1个免费的模板,1个收费的模板(2元)都不是凡品,光速套用,轻松工作。1cb946945a74cdb7db3b332f17b5ad81.gif

    展开全文
  • 本文演示如何使用Oracle数据库的over分析函数满足业务提出的数据分析需求(查询按机构,分类查询指定月份的环比同比数据)。 over函数的具体使用方法请参考博文 概念分析 进行数据分析的第一步一定是弄明白业务...

    本文演示如何使用Oracle数据库的over分析函数满足业务提出的数据分析需求(查询按机构,分类查询指定月份的环比,同比数据)。
    over函数的具体使用方法请参考博文

    概念分析

    进行数据分析的第一步一定是弄明白业务人员的意图,搞清楚业务提出的相关概念。
    环比:本月相比上个月的业务增长量与上个月业务量的百分比值。
    同比:本月相比上一年度同一月份的业务增长量与上一年度同一月份业务量的百分比值。

    设计算法

    业务分析需求的主要指标是选定月相关机构和类别的销售量和上一个月或者上一年度同一月份同一机构和类别销售量(即机构类别的月销售量),然后求解相关数值的比值。

    算法

    1,对销售数据按照机构,类别和月份进行汇总。
    2,使用over函数求当前月份的前1条和前12条数据值。
    3,计算相关数据间的比值,显示结果。

    数据质检

    本文争对的数据量少(千万级)计算成本低(秒级)可以通过直接运行算法验证,如果数据量大计算成本高请务必先对算法进行分析并选择合适的数据验证方式。

    数据质量报告

    1,分组数据量不平衡,相邻数据差异很大。
    2,存在数据量为0情况,可能导致除数为0的异常。
    3,分组连贯性没法保证,使用over函数会出现取错数据。

    解决方案

    1,争对除数可能问0的异常选择计算前对除数进行判断(使用Oracle的decode函数)。
    2,争对分组不连贯的问题适当的做数据填充。

    编写算法

    表结构设计

    设计待分析数据表结构:
    #tb_temp

    create table tb_temp
    (shop integer,
    category integer,
    salevalue number(20,2),
    month integer);
    

    #tb_analysis

    create table tb_analysis
    (shop integer,
    category integer,
    salevalue number(20,2),
    onmonth number(20,2),
    onyear number(20,2),
    month integer);
    
    数据抽取及填充

    #抽取临时数据

    insert into tb_temp
      (shop, category, salevalue, month)
      select shop, category, sum(salevalue), to_number(to_char(saledate, 'yyyymm'))
        from tb_sale
       group by shop, category, to_number(to_char(saledate, 'yyyymm'));
    

    #填充数据
    #使用存储过程
    本文只是一个简单的填充数据过程,待读者根据实际需求完善(异常处理,日志记录等)。

    create or replace procedure pk16_padd as
      months number;
      sdate  date;
      n      integer;
      cursor cs_groups is
        select distinct shop, category from tb_sale;
    begin
      select trunc(min(saledate), 'month'),
             ceil(months_between(max(saledate), min(saledate)))
        into sdate, months
        from tb_sale;
      for sc in cs_groups loop
        n := 0;
        while n <= months loop
          insert into tb_temp
            (shop, category, salevalue, month)
          values
            (sc.shop,
             sc.category,
             0,
             to_number(to_char(add_months(sdate, n), 'yyyymm')));
          n := n + 1;
        end loop;
      end loop;
      commit;
    end;
    

    #抽取分析数据

    insert into tb_analysis
      (shop, category, salevalue, onmonth, onyear, month)
      select shop,
             category,
             salevalue,
             lag(salevalue, 1, 0) over(partition by shop, category order by month) onyear,
             lag(salevalue, 12, 0) over(partition by shop, category order by month) onmonth,
             month
        from tb_temp
    
    
    数据分析
    select shop,
           category,
           salevalue,
           decode(onmonth,
                  0,
                  '∞',
                  round((salevalue - onmonth) / onmonth * 100, 2)) onmonth,
           decode(onmonth,
                  0,
                  '∞',
                  round((salevalue - onyear) / onyear * 100, 2)) onyear,
           month
      from tb_analysis
    
    展开全文
  • 同比环比

    2010-03-10 17:49:48
    同比即本年某月与上年同期相比,如:2009年2月份与2008年2月份的工业产值的比较,简称同比环比即循环不间断地比较:即本年度与上年度、本...比如刚刚公布的海关四月份进出口数据同比就是和2008年4月份相比,环比...

空空如也

空空如也

1 2 3 4
收藏数 63
精华内容 25
关键字:

年度数据同比环比