精华内容
下载资源
问答
  • IFNULL() 函数用于判断第一表达式是否为 NULL,如果为 NULL 则返回第二参数的值,如果不为 ...-- 查询指定月份的活跃 create or replace TEMPORARY view v1 as select '${ym}' , ifnull(tt.appid ,'NULLL') ...

    IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。

    -- 使用数据库
    use big12_umeng ;
    
    -- 查询指定月份的活跃数
    create or replace TEMPORARY view v1 as
    select
      '${ym}' ,
      ifnull(tt.appid ,'NULLL') appid  ,
      ifnull(tt.appplatform ,'NULLL') appplatform  ,
      ifnull(tt.brand ,'NULLL') brand  ,
      ifnull(tt.devicestyle ,'NULLL') devicestyle  ,
      ifnull(tt.ostype ,'NULLL') ostype  ,
      ifnull(tt.appversion ,'NULLL') appversion  ,
      count(tt.deviceid) cnt   #设备总数 
    FROM
    (  <font color="red"#到tt那的</font>
      select
        t.appid ,
        t.appplatform,
        t.brand ,
        t.devicestyle,
        t.ostype ,
        t.appversion ,
        t.deviceid
      FROM
      (
        select
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion ,
          deviceid
        from
          appstartuplogs
        WHERE
        #ym <= '201805'  查询20185月份的数 
          ym = '${ym}'
        group BY
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion,
          deviceid
        with cube
      )t
      where
        t.appid is not null
        and t.deviceid is not null
    )tt
    group BY
      tt.appid ,
      tt.appplatform,
      tt.brand ,
      tt.devicestyle,
      tt.ostype ,
      tt.appversion
    order by
      tt.appid ,
      tt.appplatform,
      tt.brand ,
      tt.devicestyle,
      tt.ostype ,
      tt.appversion;
    
    
    -- 查询指定月份之前的设备数
    create or replace TEMPORARY view v2 as
    SELECT
      t.appid,
      ifnull(t.appplatform,'NULLL') appplatform ,
      ifnull(t.brand ,'NULLL') brand,
      ifnull(t.devicestyle,'NULLL') devicestyle,
      ifnull(t.ostype,'NULLL') ostype ,
      ifnull(t.appversion,'NULLL') appversion,
      count(t.deviceid) cnt
    FROM
    (
      select
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion,
          deviceid
      FROM
        appstartuplogs
      WHERE
        ym <= '${ym}'
      GROUP BY
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion,
          deviceid
          with cube
    )t
    WHERE
      t.appid is not null
      and t.deviceid is not NULL
    group BY
      t.appid ,
      t.appplatform,
      t.brand ,
      t.devicestyle,
      t.ostype ,
      t.appversion
    order BY
      t.appid ,
      t.appplatform,
      t.brand ,
      t.devicestyle,
      t.ostype ,
      t.appversion ;
    
    -- 创建月活率表
    create table if not exists stat_actratio_month(
      month string ,
      appid string,
      appplatform string,
      brand string ,
      devicestyle string,
      ostype string ,
      appversion string ,
      cnt float
    )
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    lines terminated by '\n';
    
    -- 查询指定月份的月活率
    insert into stat_actratio_month
    select
      '${ym}',
      v1.appid,
      v1.appplatform ,
      v1.brand ,
      v1.devicestyle,
      v1.ostype ,
      v1.appversion ,
      v1.cnt / v2.cnt
    from
      v1 ,v2
    where
      v1.appid = v2.appid
      and v1.appplatform = v2.appplatform
      and v1.brand = v2.brand
      and v1.devicestyle = v2.devicestyle
      and v1.ostype = v2.ostype
      and v1.appversion = v2.appversion ;
    
    -- 输出结果
    select * from stat_actratio_month
    
    

    1、查询8月份各维度下的月活数 t1

    use big12_umeng ;
    create table if not exists stat_act_month(
      month string ,
      appid string,
      appplatform string,
      brand string ,
      devicestyle string,
      ostype string ,
      appversion string ,
      cnt int
    )
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    lines terminated by '\n';
    
    insert into table stat_act_month
    select
    #ym = formatbymonth(-1,'yyyyMM')
    #着部分必须是活的 所以是动态传参,所有写个变量 
      '${ym}' ,
      tt.appid ,
      tt.appplatform,
      tt.brand ,
      tt.devicestyle,
      tt.ostype ,
      tt.appversion ,
      count(tt.deviceid)
    FROM
    (   #到tt那
      select
        t.appid ,
        t.appplatform,
        t.brand ,
        t.devicestyle,
        t.ostype ,
        t.appversion ,
        t.deviceid
      FROM
      (
        select
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion ,
          deviceid
        from
          appstartuplogs
        WHERE
          #ym = formatbymonth(-1,'yyyyMM')
          ym = '${ym}'
        group BY
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion,
          deviceid
        with cube
      )t
      where
        t.appid is not null
        and t.deviceid is not null
    )tt
    group BY
      tt.appid ,
      tt.appplatform,
      tt.brand ,
      tt.devicestyle,
      tt.ostype ,
      tt.appversion
    order by
      tt.appid ,
      tt.appplatform,
      tt.brand ,
      tt.devicestyle,
      tt.ostype ,
      tt.appversion
    

    在这里插入图片描述

    2、查询8月份之前各数维度下的设备总数 t2

    create or replace TEMPORARY view v2 as
    SELECT
      t.appid,
      ifnull(t.appplatform,'NULLL') appplatform ,
      ifnull(t.brand ,'NULLL') brand,
      ifnull(t.devicestyle,'NULLL') devicestyle,
      ifnull(t.ostype,'NULLL') ostype ,
      ifnull(t.appversion,'NULLL') appversion,
      count(t.deviceid) cnt
    FROM
    (
      select
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion,
          deviceid
      FROM
        appstartuplogs
      WHERE
        ym <= '${ym}'
      GROUP BY
          appid ,
          appplatform,
          brand ,
          devicestyle,
          ostype ,
          appversion,
          deviceid
          with cube
    )t
    WHERE
      t.appid is not null
      and t.deviceid is not NULL
    group BY
      t.appid ,
      t.appplatform,
      t.brand ,
      t.devicestyle,
      t.ostype ,
      t.appversion
    order BY
      t.appid ,
      t.appplatform,
      t.brand ,
      t.devicestyle,
      t.ostype ,
      t.appversion ;
    

    3、连接查询 ,按照相同的维度key,月活数 / 设备总数

    -- 创建月活率表
    create table if not exists stat_actratio_month(
      month string ,
      appid string,
      appplatform string,
      brand string ,
      devicestyle string,
      ostype string ,
      appversion string ,
      cnt float
    )
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    lines terminated by '\n';
    
    select
      '${ym}',
      v1.appid,
      v1.appplatform ,
      v1.brand ,
      v1.devicestyle,
      v1.ostype ,
      v1.appversion ,
      v1.cnt / v2.cnt
    from
      v1 ,v2
    where
      v1.appid = v2.appid
      and v1.appplatform = v2.appplatform
      and v1.brand = v2.brand
      and v1.devicestyle = v2.devicestyle
      and v1.ostype = v2.ostype
      and v1.appversion = v2.appversion ;
    

    参考 每天新老用户,日活,周活,月活的hive计算
    https://blog.csdn.net/ddxygq/article/details/81391718

    展开全文
  • oracle数据库经典题目

    2011-02-17 15:05:20
    11. 查询表的总记录,可以采用_________统计函数。( C ) A. AVG(*) B. SUM(*) C. COUNT(*) D.MAX(*) 12. 要建立一语句向Types表中插入数据,这表只有两列,T_ID和T_Name列。如果要插入一行数据,这一...
  • 数据库表的转置

    2011-04-09 13:15:00
    数据库表的转置 ...在销售管理中,每月的销售数字在表中表示为一条记录,在统计的时候,需要按照产品,在一行中输出所有月份的销售统计。 当然对于一些比较死板的人来说可以说那我就做多字段来记录...

    数据库表的转置

    问题

    在工作中会遇到这样的问题。

    一个学生有多科成绩,每科成绩记录在表中为一条记录,那么查询出来之后,要把一个学生所有科目的成绩打印成一行,即把表中的多条记录合成一条记录。

    在销售管理中,每个月的销售数字在表中表示为一条记录,在统计的时候,需要按照产品,在一行中输出所有月份的销售统计。

    当然对于一些比较死板的人来说可以说那我就做多个字段来记录就是了,但是这样的扩展性就很差了。如果科目变了呢,你不得改表结构吗?

    准备数据

    下面我们以销售为例,首先创建如下的表。

    create table Orders
    (
    	ProductID  int,
    	OrderMonth int,
    	SubTotal    money
    )
    

    表中的每一行表示一个产品每月的销售情况。

    然后,插入若干数据。

    insert into Orders ( ProductID, OrderMonth, SubTotal )
    select 1, 5,  100.00  union all
    select 1, 6,  100.00  union all
    select 2, 5,  200.00  union all
    select 2, 6,  200.00  union all
    select 2, 7,  300.00  union all
    select 3, 5,  400.00  union all
    select 3, 5,  400.00  
    

    此时,表中的数据如下所示。

    2011040912283610.png

    现在,我们需要统计每种产品在每个月的销售情况。

    SQL Server2005 中的方式

    使用在 SQL Server2005 中提供的 Pivot 进行转置。

    SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月
    FROM 
    Orders PIVOT
    (
    SUM (Orders.SubTotal)
    FOR Orders.OrderMonth IN
    ( [5], [6], [7] )
    ) AS pvt
    
    ORDER BY ProductID;
    

    Orders PIVOT 表示对表 Orders 进行转置操作,这个表称为输入表。

    Orders.OrderMonth 称为透视列(pivot_column),FOR Orders.OrderMonth IN ( [5], [6], [7] ) 表示针对表中 OrderMonth 为 5,6,7  的月进行分组之后转置。

    SUM (Orders.SubTotal) 表示针对每组的 SubTotal 进行分组求和。SubTotal 列称为值列。

    SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月 语句中的 ProductId 与 OrderMonth 组合在一起完成分组,后面的 [5] AS 五月, [6] AS 六月, [7] AS 七月 分别对应 FOR Orders.OrderMonth IN ( [5], [6], [7] ) 中的  [5], [6], [7] 。

    Pivot 实际上按照以下的步骤完成操作:

    1. 分组求和,先按照  ProductID,OrderMonth 进行分组求和

    SELECT ProductID, OrderMonth, SUM (Orders.SubTotal) AS SumSubTotal
    FROM Orders
    GROUP BY ProductID,OrderMonth;
    

    得到的结果如下:

    2011040912402055.png

    2. PIVOT根据FOR OrderMonth IN指定的值5、6、7,首先在结果集中建立名为5、6、7的列,然后从上面的结果中取出 OrderMonth 列中取出相符合的值,分别放置到5、6、7的列中。此时得到的结果集的别名为pvt(见语句中AS pvt的指定)。结果集的内容如下所示。

    2011040912442515.png

    3.  最后根据SELECT ProductID, [5] AS 五月, [6] AS 六月, [7] AS 七月 FROM的指定,从别名pvt结果集中检索数据,并分别将名为5、6、7的列在最终结果集中重新命名为五月、六月、七月。这里需要注意的是FROM的含义,其表示从经PIVOT关系运算符得到的pvt结果集中检索数据,而不是从Sales.Orders中检索数据。

    2011040912470084.png

    Pivot 的语法如下:

    SELECT <非透视的列>,
        [第一个透视的列] AS <列名称>,
        [第二个透视的列] AS <列名称>,
        ...
        [最后一个透视的列] AS <列名称>,
    FROM
        (<生成数据的 SELECT 查询>)
        AS <源查询的别名>
    PIVOT
        ( <聚合函数>(<要聚合的列>)
    FOR
    
    [<包含要成为列标题的值的列>]
        IN ( [第一个透视的列], [第二个透视的列],  ... [最后一个透视的列])
    )
     AS <透视表的别名>
    <可选的 ORDER BY 子句>;
    

    需要注意的是:如果聚合函数与 PIVOT 一起使用,则计算聚合时将不考虑出现在值列中的任何空值。

    SQL Server 2000 中的方式

    在 SQL Server 2005 之前,通常需要通过 case 子句来处理。

    select ProductID, 
    	 sum( case when OrderMonth = 5 then SubTotal end ) as 五月,
    	 sum( case when OrderMonth = 6 then SubTotal end ) as 六月,
    	 sum( case when OrderMonth = 7 then SubTotal end ) as 七月
    from Orders
    group by ProductID
    

    补充说明:

    今天在使用 pivot 的时候,出现一个奇怪的事情,转置居然失败了!

    表的结构如下:

    create table  tbl_marks
    (
    	markId	int  identity(1,1) ,  -- 成绩的关键字
    	sid	int   ,                    -- 学生的标识
    	cid	int ,                      -- 课程的标识
    
    	mark	int,	         -- 成绩
    )
    

    表中的数据为:

    insert into tbl_marks ( sid, cid, mark ) values ( 1, 1, 100 );
    insert into tbl_marks ( sid, cid, mark ) values ( 1, 2, 90 );
    insert into tbl_marks ( sid, cid, mark ) values ( 1, 3, 95 );
    insert into tbl_marks ( sid, cid, mark ) values ( 2, 1, 60 );
    insert into tbl_marks ( sid, cid, mark ) values ( 2, 2, 61 );
    insert into tbl_marks ( sid, cid, mark ) values ( 2, 3, 99 );
    

    执行的转置语句如下:

    select  sid, [1] as [database], [2] as [CSharp], [3] as [Xml]
    from   tbl_marks pivot
    (
    	sum( mark )
    	for cid in ( [1], [2], [3] )
    ) as pvt
    

    注意,最后的 as pvt 提供的别名必须要有,虽然没有实际的用途,但是 SQLServer 2005 要求必须提供。

    结果如下:

    1	100	NULL	NULL
    1	NULL	90	NULL
    1	NULL	NULL	95
    2	60	NULL	NULL
    2	NULL	61	NULL
    2	NULL	NULL	99
    

    应该只有两行的结果,居然是六行,显然没有进行分组!

    经过分析,发现在 pivot 中,分组的依据是隐含的,pivot 将对表中除了组函数计算列和转置的列之外所有的字段进行分组,在上边的情况下,组函数对 mark 进行求值,对课程列 cid 进行转置,而表中实际有 4 列,剩下了 markit 和 sid 两列,而 markit 是一个自增长的标识列,所有的行都不相同,这样,实际上的分组并不是在 sid 一个字段上进行的,导致了错误的转置结果。

    处理的方法是将表中的列限制在 3 列,排除掉 markit 这个标识列,通过子查询可以轻松处理这个问题。

    select  sid, [1] as [database], [2] as [CSharp], [3] as [Xml]
    from  ( select sid, cid, mark from  tbl_marks) t pivot
    (
    	sum( mark )
    	for cid in ( [1], [2], [3] )
    ) as pvt
    

    注意,在 SQLServer 中子查询需要提供一个别名,虽然还是没有什么用途。  

    如果使用 case 的话,上边的转置还可以这样写。

    select [sid],  
    	max( case  when  cid = 1    then   mark end ) as [database],
    	min( case  when  cid = 2 then mark end ) as cSharp ,	-- 仅仅对课程编号是 2 的课程成绩进行求和,其实课程编号为 2 的成绩仅仅出现了一次
    												-- 所以,实际上返回的就是课程 2 的成绩
            avg( case  when cid  = 3 then mark end ) as xml,
            sum( mark ) as [总分]
    from tbl_marks
    
    group by [sid]	
    

    在这个例子中,组函数不仅可以使用 sum, 其实使用 max, min, avg 都可以,你知道为什么吗?  

      

     

    参考文献

    详细的 case 使用说明可以参考 钱途无梁 的 sql 中 case when 语法

    http://www.cnblogs.com/qiantuwuliang/archive/2009/06/03/1495770.html

    本文主要参考一下文章:

    张洪举的文章:在SQL Server 2005中实现表的行列转换

    http://blog.csdn.net/zhanghongju/archive/2006/06/02/769445.aspx

    MSDN: 使用 PIVOT 和 UNPIVOT

    http://technet.microsoft.com/zh-cn/library/ms177410.aspx

    Sman Sky :表中数据转置(Pivot)在Sql Server 2000 和Sql Server 2005 的实现

    http://www.cnblogs.com/huangbaixun/archive/2008/07/26/1252002.html

    posted on 2011-04-09 13:15 冠军 阅读(...) 评论(...) 编辑 收藏

    展开全文
  • declare @assCount int --显示项个数 set @assCount=0 --declare @subTotalSql nvarchar(2000) --小计更新 declare @subtotalSelectSql nvarchar(2000) --小计字段cdept_id|cdept_id,cperson_id --set @...
  • 实例235 嵌套查询在查询统计中的应用 7.13 子查询 实例236 用子查询作派生的表 实例237 用子查询作表达式 实例238 在Update语句中应用子查询 7.14 联合语句UNION 实例239 使用联合查询 实例240 多表联合查询...
  • 实例125 统计数组元素个数 156 实例126 向数组中添加元素 156 实例127 将数组中指定索引位置的元素替换 157 实例128 获取数组中最后一个元素 158 实例129 去除数组中的重复元素 158 实例130 字符串与数组的转换 159 ...
  • 实例125 统计数组元素个数 156 实例126 向数组中添加元素 156 实例127 将数组中指定索引位置的元素替换 157 实例128 获取数组中最后一个元素 158 实例129 去除数组中的重复元素 158 实例130 字符串与数组的转换 159 ...
  • 实例032 判断用户输入月份的季节 2.4 循环控制 实例033 使用while与自增运算符循环遍历数组 实例034 使用for循环输出杨辉三角 实例035 使用嵌套循环在控制台上输出九九乘法表 实例036 用while循环计算1+1/2!+1...
  • 实例032 判断用户输入月份的季节 2.4 循环控制 实例033 使用while与自增运算符循环遍历数组 实例034 使用for循环输出杨辉三角 实例035 使用嵌套循环在控制台上输出九九乘法表 实例036 用while循环计算1+1/2!+1...
  • 冲浪-源码

    2021-02-28 03:17:24
    重构代码以提供有关夏威夷瓦胡岛6月和12月月份温度趋势的查询结果,以确定一家全年冰淇淋店的可行性。 资源 数据源:hawaii.sqlite 软体:Python,Jupyter Notebook,Pandas,SQLAlchemy 概述 该分析由使用hawaii....
  • 本书侧重于函数的实战应用,共分12章,前10章分别介绍了数学函数、统计函数、日期和时间函数、文本函数、逻辑函数、查找和引用函数、财务函数、信息函数以及数据库和三角函数在实战中的应用;第11 章介绍了函数与...
  • 实例032 判断用户输入月份的季节 42 2.4 循环控制 43 实例033 使用while与自增运算符循环遍历 数组 43 实例034 使用for循环输出杨辉三角 43 实例035 使用嵌套循环在控制台上输出 九九乘法表 44 实例036 用while循环...
  • 实例032 判断用户输入月份的季节 42 2.4 循环控制 43 实例033 使用while与自增运算符循环遍历 数组 43 实例034 使用for循环输出杨辉三角 43 实例035 使用嵌套循环在控制台上输出 九九乘法表 44 实例036 用while循环...
  • 实例112 计算两日期间的月份数 第5章 复杂查询技术 5.1 使用子查询 实例113 将子查询作为表达式 实例114 用子查询作为派生表 实例115 通过子查询关联数据 实例116 使用IN谓词限定查询范围 实例117 使用NOT IN子...
  • Java开发实战1200例.第2卷.part3

    热门讨论 2013-05-08 22:46:34
    实例112 计算两日期间的月份数 192 第5章 复杂查询技术 194 5.1 使用子查询 195 实例113 将子查询作为表达式 195 实例114 用子查询作为派生表 196 实例115 通过子查询关联数据 197 实例116 使用IN谓词限定查询范围...
  • Java开发实战1200例.第2卷.part2

    热门讨论 2013-05-08 22:45:35
    实例112 计算两日期间的月份数 192 第5章 复杂查询技术 194 5.1 使用子查询 195 实例113 将子查询作为表达式 195 实例114 用子查询作为派生表 196 实例115 通过子查询关联数据 197 实例116 使用IN谓词限定查询范围...
  • Java开发实战1200例.第2卷.part1

    热门讨论 2013-05-08 22:44:13
    实例112 计算两日期间的月份数 192 第5章 复杂查询技术 194 5.1 使用子查询 195 实例113 将子查询作为表达式 195 实例114 用子查询作为派生表 196 实例115 通过子查询关联数据 197 实例116 使用IN谓词限定查询范围...
  • 说明: 指定数据库的默认语言, 该语言将用于消息, 日期和月份名, AD, BC, AM 和 PM 的符号, 以及默认的排序机制。可支持的语言包括英语, 法语和日语等等。 值范围: 任何有效的语言名。 默认值: 根据操作系统而定 ...
  • SQL语法大全

    2014-03-30 11:00:11
    这里的ActiveConnection可以是一Connection对象或是一串包含数据库连接信息(ConnectionString)的字符串参数。 CursorType Recordset对象Open方法的CursorType参数表示将以什么样的游标类型启动数据,包括...
  • 今年9月份参加计算机二级考试的同学请转载 作者:天使~之戀 已被分享1次 评论(0) 复制链接 分享 转载 删除 下面的138道题目,在二级考试中命中率极高。 一、选择题 (1) 下面叙述正确的是(C) A. 算法的执行效率与...
  • C#编程经验技巧宝典

    热门讨论 2008-06-01 08:59:33
    54 <br>0075 用回溯法找出n个自然数中取r个数的全排列 55 <br>0076 约瑟夫环问题 56 <br>0077 猴子选大王 57 <br>0078 如何判断IP是否正确 57 <br>0079 如何将小写金额转换为大写金额 57...
  • 语音合成与逻辑循环回顾语音合成简单理解while控制次数WhileElseWhileElse实战whileFloat回顾与ifelse表达式赌博概率分类设计方法-一元二次方程嵌套求有序3个数闰年计算矩形区域随机数ifelse回文数判断10进制转化16...
  • 实例154 数据库查询的方法 实例155 带参数的数据查询 实例156 向数据表中插入数据的方法 实例157 数据修改的方法 实例158 数据删除的方法 实例159 数据分页的方法 实例160 对结果集进行分页的方法 实例161 ...

空空如也

空空如也

1 2 3
收藏数 58
精华内容 23
关键字:

数据库查询统计月份个数