比如一张表里有如下时间字段的记录： 2009-01-01 12:00:00 2008-12-23 11:00:11 2009-12-22 11:22:0...
Power BI一招帮你搞定 在实际业务中，新用户很重要，但是如何留存老用户更是一个巨大的课题，总的来讲，就是提升服务质量，增强满意度，具体细分在各个行业，运营思路千差万别，我们今天不详细展开。...但有一点是几乎所有行业都想通的，就是我们要对沉睡、流失的客户进行回访，分析，想尽办法进行唤醒、激活或重新购买。Power BI如何帮助业务人员进行统计汇总呢？...计算每一个客户最后一次订单的日期； 日期如果落在最近6个月到2个月之间，就是我们想要的流失客户 直接给出度量值： LOST CUSTOMERS = VAR customer_lastsale=...同样，他在8月和9月也未发生订单，所以在9月也是睡眠用户，而到了11月，已经过了6个月内有订单了，他就真的属于流失客户了。...在日常的运营管理中，我们经常会遇到想要查看某个时间段的用户在下一个时间段的复购情况，而且时间段是任意的，可以按月，可以按周，可以任意选择时间段，那么这个该如何实现呢？我们下期再见。
2.计算R、F、M值 根据RFM分析方法指标的定义，只需要“用户行为类型”中的“购买”值，可以先筛选(where)出用户的所有“购买”记录（下图红框的地方），做为临时表a。...R值=现在（2020-01-30）- 每个用户最后一次购物日期 每个用户最后一次购物日期，涉及到“每个问题“要想到《猴子 从零学会SQL》里讲过的用分组汇总来实现。...根据“用户编号”分组，然后汇总（最后一次购买日期，就是找到日期的最大值，用汇总函数max） select 用户编号, max(用户活跃日期) as 最后一次购物日期 from a group by a...“每位用户”按用户编号分组（group by），然后用汇总函数（count）求出购买次数。...select 用户编号 from a where datediff('2020-01-30',用户活跃日期)<=30; 然后，分析出“每位用户的总消费金额”，按用户编号分组（group by），用汇总函数
、月度/年度收支统计等，需根据查询时间范围确定涉及的集群范围，并将多个集群的查询结果在组件的结果集归并模块中按分组条件进行汇总归并；轮询路由-追加/汇总归并：对应单笔/多笔查询、修改场景，由于输入参数能够定位数据的只有非交易时间类字段...图 3:代码逻辑架构整体逻辑架构包括了应用框架、业务代码、以及以 jar 包形式集成在应用框架和业务代码中的数据路由 SDK。...第一段先通过请求参数中的业务类型、查询的日期范围，结合动态计算的每个集群当前的上下界时间，确定当前请求涉及的集群范围。...如表 1 所示：多数据源 SQL 执行：透传回调：当路由解析结果只涉及热集群时，直接透传返回、并在不改写 SQL 参数的情况下回调原始 SQL；多数据源执行：按路由解析排序后的结果在多个集群依次执行 SQL...最后会按集群维度将结果集追加合并；结果集归并：分页查询场景：对于按交易日期排序的简单场景，只需将多个集群的结果集按顺序追加归并即可。
窗口函数 群组分析方法对应到SQL里常用窗口函数来实现。也就是从某些维度对数据分组（partition by），然后同样也可以对每个组进行统计运算。...首先要获取“当日首次购买用户量”，也就是获取每个用户的第一次购买的日期（也就是对用户按购买时间排名，排名第1的就是第一次购买的日期）。...此处为了分别获取每个用户在次月、第三月、第四月的购买记录，需要与“购买记录表”联结三次，并且在最终按日期进行汇总分析。...)) = 3 and t1.用户id = t4.用户id group by t1.日期 order by t1.日期; 【本题考点】 1.遇到复购、留存、流失这类业务问题，要想到用群组分析方法 2.群组分析方法对应在...SQL里用窗口函数实现 3.SQL常用函数的使用，包括：count、date、timestampdiff、distinct。
，所以我们说成3+1 这里说的是BI中的JOIN，并不是SQL中全部的JOIN，有些关联计算仍然需要原始的JOIN定义来描述，比如做矩阵乘法，但在BI中碰不到 我们针对这3+1种情况，重新定义JOIN运算...，主键相同，在数据库设计中经常有这种情况，字段的业务分类不同，不适合都放在一个表里，太宽的表在各字段丰满度相差较大时还会造成空间冗余浪费，访问性能也下降，因此常常会分到多个主键相同的表中 现在我们要查询计算所有员工的收入...，SELECT后直接再加字段就可以了 按维对齐 这里有三个表：合同表、回款表和库存表 我们希望按日期统计合同额、回款额和库存金额 用SQL写出来是这样的： SELECT T1.日期,T1.金额,T2....中，只要把这几个表分别按日期对齐分别汇总就行了，而不必关心这些表之间的关系，在增删表时也不容易发生遗漏 如果按维对齐再与外键搅到一起，情况就会更复杂： 我们希望按地区统计销售员人数和合同额 用SQL写出来是这样...---按日期统计3个不同表的汇总金额 这个虽然还是三个表，但业务人员也不用管各个表之间有什么关联关系，找到对应的金额指标，选择求和，然后直接拖拽就可以，再选一个“日”当做共同的统计条件，那就是按日期汇总了
最近项目需要统计一段日期范围内，根据每分钟、几分钟、每天分别统计汇总某些事件/指标的发生总次数，平均发生次数，因此总结了Mysql中与时间处理、统计相关的资料。...2017-05-31补充一下支持跨天的按照一定时间间隔统计汇总数量的SQL，例子是按照15分钟汇总统计 select count(1), from_unixtime( round(unix_timestamp...date_fromat(date, format) TIMSTAMPADD 按照输入的间隔对日期进行计算。...datetime_expr) select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00 TIMESTAMPDIFF Mysql中计算两个日期的时间差函数...参考资料： 1、Mysql中Timstampdiff的用法 2、Mysql的时间、日期函数 3、Mysql Date Time Functions
3+1 这里说的是BI中的JOIN，并不是SQL中全部的JOIN，有些关联计算仍然需要原始的JOIN定义来描述，比如做矩阵乘法，但在BI中碰不到 我们针对这3+1种情况，重新定义JOIN运算，改造SQL...，主键相同，在数据库设计中经常有这种情况，字段的业务分类不同，不适合都放在一个表里，太宽的表在各字段丰满度相差较大时还会造成空间冗余浪费，访问性能也下降，因此常常会分到多个主键相同的表中 现在我们要查询计算所有员工的收入...，SELECT后直接再加字段就可以了 按维对齐 这里有三个表：合同表、回款表和库存表 我们希望按日期统计合同额、回款额和库存金额 用SQL写出来是这样的： SELECT T1.日期,T1.金额,T2....中，只要把这几个表分别按日期对齐分别汇总就行了，而不必关心这些表之间的关系，在增删表时也不容易发生遗漏 如果按维对齐再与外键搅到一起，情况就会更复杂： 我们希望按地区统计销售员人数和合同额 用SQL写出来是这样...---按日期统计3个不同表的汇总金额 这个虽然还是三个表，但业务人员也不用管各个表之间有什么关联关系，找到对应的金额指标，选择求和，然后直接拖拽就可以，再选一个“日”当做共同的统计条件，那就是按日期汇总了
但分析师日常使用最多的还是Hive，因此本文就将日常工作的Hive查询重难点做个汇总，分享给大家～ ⚠️注意：这里不是介绍SQL的基础，基础知识在上期【数据分析师的必要条件】已经提及。...尤其是正则替换和正则提取，在日常业务中使用频率极高，所以掌握一定的正则知识是必要的。限于篇赋，这两点在后续的SQL实战中再做详细介绍。...在做日期计算时，建议先将日期转为月初/年初计算（使用trunc函数即可），最后再进行截取操作，因为截取后的日期无法被识别为日期，在后续计算又得concat回来。...Hive的wih table_name as 主要是用来优化SQL的。因为在业务中，有些SubQuery需要被反复使用，但使用场景也仅限于当前SQL，为此开发中间表就显得不值当。...因此就可以通过wih table_name as在SQL中构建临时表（类似于函数），方便后续调用，这样就极大的提升了代码的整洁性和可读性。
3.分析每个月按领红包取天数为1、2、3……30、31天区分，计算取每个月领取红包的用户数，人均领取金额，人均领取次数。...当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总来解决这样的问题。 用“日期”来分组（group by），用count(用户ID)来汇总登录用户。...从a表中，用分组汇总，求出每类用户的个数，人均领取金额，人均领取次数。...出现“每个月”这样的字眼，和就要想到用分组汇总来解决此类问题，按“月份”分组。 使用month(日期)获取月份。...ID = b.用户ID; 如果“领取红包”表中的用户为非空，那么该用户是“领过红包用户”（也就是在“领取红包”表中匹配到了数据）；如果“领取红包”表中的用户为空的，那么该用户是“未领取红包用户”（也就是在
需要特别说明的是，JsonPath/XPath可以灵活表达条件查询的层级范围，包括绝对位置、相对位置、父节点、子节点、属性、元素等，这是多层数据处理语言有别于二维数据处理语言（SQL）之处，如代码中的$...SPL具有足够的计算能力 esProc SPL是JVM下开源的结构化数据/多层数据处理语言，内置专业的多层数据对象，提供了丰富的计算函数、字符串函数、日期函数，具有不亚于SQL的计算能力...此外，SPL还支持在条件查询中使用数学运算符（函数）、位置函数、日期函数。SPL可以灵活地访问不同层级，且代码更简单，如代码中的A2.conj(Orders)。 ...SPL实现各类聚合计算也很简单，比如求和：=A3.sum(Amount) SPL支持丰富的基础计算，具有不亚于SQL的计算能力，比如JsonPath/XPath必须硬编码实现的分组汇总，...“|”) //返回[“aa”,“bb”,“cc”] SPL还支持年份增减、求年中第几天、求季度、按正则表达式拆分字符串、拆出SQL的where或select部分、拆出单词、按标记拆HTML
前面几讲内容，我们分别介绍了新用户和流失客户的分析 【运营】新用户数量？Power BI简单三步计算 【运营】新用户明细？Power BI一招帮你搞定 【运营】沉睡、流失客户分析？...Power BI一招帮你搞定 在日常的运营管理中，我们经常会遇到想要查看某个时间段的用户在下一个时间段的复购情况，而且时间段是任意的，可以按月，可以按周，可以任意选择时间段，那么这个该如何用Power...我们先整理一下思路： 既然是任意选择时间段，那么切片器一定是直接用日期切片器，选择范围。 前一个日期范围和后一个日期范围，所以需要同时有两个切片器。...我们要做的就是添加几个度量值： 日期1范围的客户 日期1范围的客户在日期2中也产生了订单 以上两个的百分比 我们直接给出度量值： 日期1的客户数量 = CALCULATE( DISTINCTCOUNT...再用COUNTROWS计算多少行，就是复购的数量，再除以日期1的客户数量，就得到了【复购率%】。 放到矩阵中： ? 这样，我们随意拖动两个滑竿，就能实现按年、季度、月、周等任意时间段的复购情况。
在保证信息完整性的同时尽量缩减数据规模，提高后续人群创建的效率 生产对齐 不同标签数据表产出时间不同，人群圈选如果明确了日期范围，那么需要对齐所有标签日期范围 宽表的生成依赖上游各标签数据表的就绪，宽表某日期下的数据对应到每一个标签下时其日期一致...最简单直接的方式是通过SQL语句来拼接各类标签源数据表，图5-5展示了将多个标签汇总到画像宽表的主要流程。...随着业务发展，生产画像宽表所涉及的标签数量逐渐增加，仅通过一条SQL语句生成宽表的缺陷逐渐暴露出来。首先SQL语句随着标签的增多会变冗长且结构复杂，在SQL中增删改标签的难度增大，提高了维护成本。...其次每个标签Hive表的就绪时间不同，单条SQL语句执行模式会等待所有标签就绪，这就造成宽表的产出时间受最晚就绪的标签影响，而且在SQL执行时涉及所有上游标签数据，其需要大量的计算资源集中进行计算，这无疑会造成宽表的产出时间延长...为了解决这个问题也可以将标签拆分到两个小宽表中，与日期无关的标签单独放一张宽表且仅保留最新日期的数据；与日期有关的标签放到另外一张宽表中，且按日期保存一段时间的数据。
作者：vivo 互联网大数据团队- Wu Yonggang 在《用户行为分析模型实践（一）—— 路径分析模型》中，讲述了基于平台化查询中查询时间短、需要可视化的要求，并结合现有的存储计算资源以及具体需求...定义如下： 无序漏斗：在漏斗的周期内，不限定漏斗多个步骤之间事件发生的顺序。 【计算规则】：假设一个漏斗中包含了 A、B、C 3个步骤，A步骤发生的时间可以在B步骤之前，也可以在B的后面。...将表3.1中的数据每步按照日期加起来，就得到2021-05-27至2021-05-29日数据的漏斗图（图3.2）。...在模型的设计上，采用的思想是： 在一个周期内，按照步骤顺序依次计算漏斗每一步骤的人数，并且下一层的计算的人群范围要等于上一次计算完成的人群范围，通过每一步的人群范围可以计算出想要的指标，比如每步的人数（...按天统计，计算出每天的用户数及每个层级的pv，uv。
二、数据存储 大多数公司的业务系统都是存储在mysql之类的关系型数据库中，通常在项目初期，全球化问题暂时不会考虑，部署在中国区的mysql实例，默认就是北京的东8区，即：GMT+08:00。...三、时间的匹配及展示 有了前面2个前提，后面的事情就好做了，先来看日期字段的sql where 匹配： 3.1 根据查询参数中的timeZone，把传入的日期字符串，视为当地时间，统一转换成北京时间（在...java层做转换即可，文章最后会给出转换代码），这样就跟db中的时区一致，原来的sql语句不用任何调整. 3.2 在数据展示时，把db中查出来的时间(默认北京时间)，根据timeZone转换成当地时间显示...四、一些按天汇总的job调整 有些报表，是按“自然天”跑定时job汇总统计，比如每天统计 当地时间0点到23:59:59的订单总数。...所以，还有另一个思路：把按天计算的报表，汇总的时间颗粒度细化，变成按小时计算，每个小时汇总前1个小时的数据，1个小时一条记录，然后不同时区的用户在查看时，根据当地自然天，查询出对应匹配的24条记录，最后做个简单的
image.png 【面试题】 有一张“快递揽收表”，包含3列：运单号、客户id、创建日期。 image.png 问题：查询运单创建日期在0501-0531期间不同单量区间的客户分布。...汇总分析 计算0501-0531期间每个客户的单量，涉及到“每个”，要想到《猴子 从零学会SQL》里讲过的用分组汇总来解决这类问题。...image.png 在上一步查询结果的基础上（作为子查询），再使用一次汇总分析，按“单量区间”分组（group by），对客户id汇总得到客户数（count）。...SQL语句，得到“最终结果”： image.png 按《猴子 从零学会SQL》里教的，把“最终结果”导出到Excel里，对数据进行可视化可以更方便的观察数据。...以及它们的组合使用 4.当遇到“区间”问题的时候，要想到用多条件判断（case语句）解决 5.考察遇到业务问题，如何用逻辑树分析方法把复杂问题变成矿业解决的子问题 image.png 【举一反三】 查询运单创建日期在
第一列为QQ，第二列为性别，第三列为活跃度，最后一列用户是否流失。我们要解决一个问题：性别和活跃度两个特征，哪个对用户流失影响更大？我们通过计算信息熵可以解决这个问题。...使用Hive SQL实现信息熵的计算 从表2中我们不难发现，在计算信息熵和信息增益之前，需要对各维度做汇总计数，计算各公式中出现的分母。...Hive SQL中，cube能帮助我们很快的做汇总计算，话不多说直接上代码： SELECT t1.feature_name, SUM((ea_all/es)*EA) as gain, SUM(NVL(...KeyStep2：各feature下的信息增熵 信息增益计算结果： 结束语： 以上为信息熵计算过程的SQL版本，其关键点在于使用cube实现了feature和label所需要的汇总计算。...需要的同学只需要按照规定的表结构填入数据，修改SQL代码即可计算信息增益。文中如有不足的地方，还请各位指正。
“每天的司机数”，司机数的计算用到的表是 “司机数据” 表。当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总，来解决“每天”这样的问题。...用“日期“来分组（group by），用 count(司机id) 来汇总司机数。 “各城市”，城市在“城市匹配数据“表中。也就是“每个城市”所以用“城市“来分组（group by）。...当出现“每天”要想到《猴子 从零学会sql》里讲过的分组汇总，来解决“每天”这样的问题。用“日期“来分组（group by），用 sum(流水) 来汇总流水。...订单量的计算，会用到 “订单数据” 表，用count(订单id)来计算。然后思路与上题一样，新司机在 “在线时长数据” 表中并没有，而是在 “司机数据”表 中通过查询语句才能得到。...司机数的计算用count(司机id)，用到的是“司机数据”表，城市名称在 “城市匹配数据”中，用表的联结。联结图如下。
在上例中，只对 prod_price列指定DESC，对prod_name列不指定。因此， prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准 的升序排序。...(默认升序) 在字典（dictionary）排序顺序中， A被视为与a相同，这是MySQL （和大多数数据库管理系统）的默认行为。...在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。 IN操作符一般比OR操作符清单执行更快。...() 返回日期时间的日期部分 DateDiff() 计算两个日期之差 Date_Add() 高度灵活的日期运算函数 Date_Format() 返回一个格式化的日期或时间串 Day() 返回一个日期的天数部分...如果在GROUP BY子句中嵌套了分组，数据将在后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 （所以不能从个别的列取回数据）。
（全匹配） 4.确定集合（全匹配） 5.模糊查询（部分匹配） 6.空值查询 统计汇总查询 分组查询 排序查询结果 单关系（表）数据查询结构 查询结果仍为表，WHERE、SELECT 分别相当于关系代数中的...-- 指定数据库 SELECT sno,sn,birthday -- 查询信息指定 FROM tb_student -- 指定表 计算成绩表中学生年龄，并用学号、姓名、年龄做表头 方法：当前日期减去生日日期求得年龄...tb_student where (sex='男' AND dept='软件学院') AND (polity='党员') 3.确定范围（全匹配） 查询成绩表中，成绩段在 70~90 之间的学生学号和学分信息...] 表示在某范围的字符 [0~10] 范围字符，仅匹配一个字符 [^ ] 表示不在某范围的字符 不在 [0~10] 范围内字符 查询所有姓王的学生姓名和学院信息 select sn，dept from...常用库函数（聚合函数）： 函数名称 功能 AVG 按列求平均值 SUM 按列求和 MAX 求列最大值 MIN 求列最小值 COUNT 按列求个数 count(*) 对表中数目进行计数，无论是否为空
领取专属 10元无门槛券
手把手带您无忧上云
云服务器
ICP备案
腾讯会议
对象存储
云直播
