PersephoneSu

原创 【SQL】HiveSQL 转置/列转行/行转列 map/case when

EXCEL的转置功能列转行SELECT `项目`,`类型`,`值`FROM(select `项目`,map(‘申请率’,`申请率`,‘审批率’,`审批率`) tmpfrom tmp_table)LATERAL VIEW EXPLODE(tmp) explode_table AS `类型`,`值`转置前转置后行转列SELECT `类型`,sum( case when `项目` = ‘甲’ then `值` else 0 end ) `甲`,sum( case when `

原创 【SQL】Hive/Spark SQL 环比/同比日期/周期汇总

当天‘${zdt.format(“yyyy-MM-dd”)}’昨天‘${zdt.addDay(-1).format(‘yyyy-MM-dd’)}’前30天‘${zdt.addDay(-30).format(‘yyyy-MM-dd’)}’本周-上周四至本周三betweendate_sub(’${zdt.format(“yyyy-MM-dd”)}’,’${zdt.format(“u”)}’+3)anddate_sub(’${zdt.format(“yyyy-MM-dd”)}’,’${zdt.

原创 ODPS SQL 优化函数

111

原创 【SQL】order by自定义排序，指定排序，复杂排序

写代码前的知识准备：sql执行顺序：from→where→group by→having→select→order by→limitgroup by的字段必须出现在select字段中，order by可以不必出现在select字段中且可以使用别名，为排序时减少一层select嵌套提供基础目标排序：香蕉>苹果>橘子select type,salesfrom Torder by CASE when type = '香蕉' then '1' when type = '苹果'

原创 【笔记】模型&数据挖掘基础知识-思维导图-持续更新

原创 【笔记】数据分析业务技能-思维导图-持续更新

原创 【笔记】统计学知识基础框架-思维导图-持续更新

原创 【SQL】为什么中位数sql提取可以考核多种知识点用法，row_number() over()，sum(case when ...end)

这道题在面试中出现频次较高，因为一道题既可以考核case when 用法，也可以考核窗口函数用法，还可以考核聚合函数用法，一举三得，完成这道题需要有清晰的思路，对题目的理解。首先，理解中位数的概念：是按顺序排列的一组数据中居于中间位置的数。奇数情况：由低到高排序，取中间那个值。偶数情况：由低到高排序，取最中间的两个数值的平均数。因此sql写法要一次性考虑到两种情况，完整代码如下：select sum(case when flag1=flag2 then num else null end) num

原创 【SQL】用户连续登录天数、窗口函数

select user,count(1) cnfrom(select a.*,date_sub(date,rn) flagfrom (select user,date,row_number() over(partition by user order by date) rnfrom test) a ) a group by user,flag结果：讨论：窗口函数排序select user,date,row_number() over(partition by user order by.

原创 【Python】数据分析 聚类分析-RFM模型-KMeans聚类

KMeans聚类：中心思想是不断调整中心点来计算聚类，生成新的中心点，直到达到平衡为止。随机选取K个中心点，计算其他点到中心点的位置，并选择最近的归类，重复该过程，直到中心点不再变化。服从高斯（正态）分布。优点：只有需要调整一个参数，操作简单，...

原创 【SQL】sql 多行变一行，一行变多行，行转列，列转行，lateral view explode split，collect_set/collect_list

一行变多行，行转列一行原数据：orderid clientname123 linda,lily,bob345 apple,july,cici一行拆成多行：-- LATERAL VIEW explode 要放置于from之后，where之前select distinct orderid,clientname from table LATERAL VIEW explode (split(clientname,',')) t

原创 【SQL】hive中不支持in/not in(select...) 子查询怎么办，包含/不包含关系

A表中不包含B表中的数据原代码select *from class_awhere age not in (select age from class_b where city = 'shanghai' )left join改写：select *from class_a aleft join (elect age from class_b where city = 'shanghai') b on a.age = b.agewhere b.age i

原创 【SQL】用一条SQL语句进行不同条件统计，多条件聚合，case when ,if，sum()，count()

通过select子句进行条件分支，效率高于使用where子句表table city orderid sex 北京 2984798665 男 上海 7658545679 女 ... ... ...通过订单号统计各城市的男女性销量方法1：sum()函数聚合，此类可能会遇到没有去重问题 select city ,sum(case when sex = '男' then '1' else '0' end) as cnt

原创 【SQL】case表达式，注意事项，case when用法

简单CASE表达式CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女'ELSE '其他' END 搜索CASE表达式CASE WHEN sex = '1' THEN '男' WHEN sex ='2' THEN '女'ELSE '其他' END 伪代码：如果性别等于1那么修改为男如果性别等于2那么修改为女否则修改为其他结束注意事项：如果发现when子句为真的时候，case表达式的真假判断就会终止，而剩下的when子句就会被忽略

原创 【SQL】Spark HiveSQL常见报错

-- 无权限更新Authorization failed:No privilege 'Update' found for outputs-- 库或表对象不存在com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name-- 列数或类型不一致，无法插入表Cannot insert into target table because column number/types are different-- 表无分区FA

原创 【SQL】sql 计算时间差，单位为秒

将时间转换成时间戳格式，直接相减，得出的结果以秒为单位UNIX_TIMESTAMP(orderdate)-UNIX_TIMESTAMP(orderdate) <= 86400 – 时间相差24小时内

原创 【Python】 Pandas数据分析——导入数据EXCEL

读取excel文件设置列对齐pd.set_option('display.unicode.ambiguous_as_wide',True)pd.set_option('display.unicode.east_asian_width',True)读取工号、姓名、部门、交易额这4列数据，使用默认索引（注意样本路径的反斜杠哦）df = pd.read_excel('C:/Users/Susu/Desktop/样本.xlsx',usecols=['工号','姓名','部门','交易额'])输出

原创 【SQL】spark sql 判断星期几 周六周日 平日 周末

看了别的文章，hive判断星级几的sql比较复杂，这里写一个用正则表达比较简单的,但是需要spark引擎执行select if(date_format(date,"u") between '1' and '5','平日','周末')select date_format('2021-05-12',"u")>>3...

原创 【SQL】hive 设置变量 临时修改日期 动态改变日期

在做hive代码分析时，会经常遇到改变参数的情况，例如经常需要修改特殊日期，不同时间需要看不同时间段的数据，如果修改的地方较多，工作量就比较大且容易出错，用变量参数可以提高效率。只需要修改set中的日期值，而不用改动select代码set startday = ‘2021-04-22’;set endday = ‘2021-05-05’;select *from tablewhere to_date(orderdate) between ${hiveconf:startday} and ${hi

原创 【SQL】sql 总计、小计，汇总中的所有组合；关于rollup/cube/grouping sets/grouping全总结

数据分析中常常需要各种不同维度的汇总，下面介绍如何让sql像excel透视表一样汇总，可以节省不少行行代码使用条件：必须使用group by优化：汇总行会出现null值表示汇总，需要用到grouping一步定义null值三种汇总区别：...

原创 【SQL】定位连续区间，定位连续区间的开始值和结束值，最大值和最小值

SQL经典问题:找出连续日期及连续的天数，不同连续时间段最大时间，最小时间等。工作时有些系统不支持 in、exists 等子查询，不支持笛卡尔积，这时用窗口函数会比较容易解决。主要用到的窗口函数：sum() over( order by …);lag() over( order by …)let’s do it案例：有一组数据，第一列是酒店类型A/B，第二列是操作时间，怎么取出不同酒店类型不同时间段的最大、最小操作时间？...

原创 【R】R语言-导入EXCEL数据

library(xlsx)data <- read.xlsx(“C:\\Users\\Susu\\Documents\\画图.xlsx”,sheetName = “Sheet1”,encoding = “UTF-8”)#输入路径时，要注意反斜杠#文件太大时，可能会报错，报错如下：Error in .jcall(“RJavaTools”, “Ljava/lang/Object;”, “invokeMethod”, cl, :java.lang.OutOfMemoryError: Java .

原创 【SQL】生成开始日期至结束日期之间的日期，日期递归，日期填充，日期循环SQL

案例开始日期：2021-01-01结束日期：2021-01-05生成结果：2021-01-012021-01-022021-01-032021-01-042021-01-05select a.id,date_add(starttime,idx) as date1from (select id,starttime,endtimefrom tmp.sgy_product )alateral view posexplode(split(space(datediff(endtime,star

原创 【SQL】hivesql建表，分区表SQL

建立分区表SET hive.exec.max.dynamic.partitions=1000;SET hive.exec.max.dynamic.partitions.pernode=1000;use products_fg; - - 选择库INSERT overwrite TABLE products.sgy_daily_productpartition(d=’${zdt.format(“yyyy-MM-dd”)}’)select插

原创 【SQL】Hive/Spark 上传线下表_建表

dfs -get hdfs://ns/user/adhoc/files/upload/orderid.txt-20201030-173836 orderid.txt;ADD FILE orderid.txt; # 手动上传文件use tmp_products;drop table if exists tmp_products.orderid_20101105...