长沙智能建站模板,国外工装设计网站大全,中信建设有限责任公司 吴方旭,seo 海外大家好#xff0c;我是明月十四桥#xff01;#xff01; 爱好特长#xff1a; 1、爱好和特长有很多#xff0c;擅长word、excel、ppt、wind等软件的安装与卸载#xff1b; 2、精通PE、PS、PB、DCF、PEG等单词的拼写#xff1b; 3、熟悉Windows、Linux、Mac、Android、I… 大家好我是明月十四桥 爱好特长 1、爱好和特长有很多擅长word、excel、ppt、wind等软件的安装与卸载 2、精通PE、PS、PB、DCF、PEG等单词的拼写 3、熟悉Windows、Linux、Mac、Android、IOS、WP8等系统的开关机。 今日重点 ① 花里胡哨、不择手段的sql写法 ② 一个深度回答把面试官征服。 白天工作晚上写文呕心沥血整理有问题欢迎评论点赞、收藏、评论是对我最大的支持 目录
一 缘起
二 火花
2.1 内置函数实现行转列
2.2 经典case when实现
2.3 Python groupby 实现列转行
2.4 Python pandas 实现列转行
2.5 execl 数据透视表实现行转列
2.6 Java 实现行转列
2.7 hive sql实现行转列
2.8 Teradata UDF实现行转列
三 阑尾 一 缘起
在我们热爱的《数据仓库交流群》里发生了一幕 请教大佬们 这个咋搞 那9个字犹如一声惊雷在这个热情的群里炸开了锅...
大佬们纷纷按奈不住尤其是我看不得别人有问题我心急如焚一是担心这位小哥哥面试受挫二是这么好的学de习se的机会我一定要把握住。。
大佬1 这位来自上海的大佬首先给出了orcale自带函数的解法... 大佬2 基于orcale自带函数的局限性大佬2提出了适用于mysql、oracle、hive的case when写法... 大佬3 hive 高级函数花里胡哨起来了... 问题 ps.哈哈哈哈这不就是10次面试9次问的行转列嘛~
讨论过程中
大佬们纷纷谏言献策集思广益。 二 火花
2.1 内置函数实现行转列
原理 SELECT *
FROM student
PIVOT (SUM(score) FOR subject IN (语文, 数学, 英语)
) #默认按照score和subject以外其它字段进行group by
结果展示 2.2 经典case when实现
使用case when来依条件分列是最简单的一种方法。
select 学生号,
sum(case 科目 when 语文 then 成绩 end) as 语文,
sum(case 科目 when 数学 then 成绩 end) as 数学,
......
from table
where ...
group by 学生号
case when进阶动态列数行转列
但是转换后列数不固定的情况下呢对照上面的例子来说就是假设我并不知道学生学了哪些科目的时候应该怎么做
我们继续用case when但是由于列的不固定需要先根据条件分支产生的可能性来拼接一下语句再动态执行。直接上码看效果可能更清晰一点
declare sql varchar(4000)
set sql select 学生号 select sql sql ,sum(case 科目 when 科目 then 成绩 end) [科目]
from (select distinct 科目 from table ) as a
order by 科目 select sql sql from table group by 学生号 --print sql
exec(sql)
2.3 Python groupby 实现列转行
df_new df_new.groupby(by电影名, as_indexFalse).agg(/.join)print(df_new)
2.4 Python pandas 实现列转行
import pandas as pddf pd.DataFrame([[夏洛特烦恼,沈腾/马丽/尹正/艾伦/王智],[缝纫机乐队,大鹏/乔杉/古力娜扎/李鸿其/韩童生]],columns[电影名,部分演员])
print(df)# 根据/拆分为列表
df[部分演员] df[部分演员].str.split(/) # 转成列表
print(df)df_new df.explode(部分演员)
print(df_new) 2.5 execl 数据透视表实现行转列
Excel 要实现行列转换需要用到 Power Query而Power Query 只有Excel 2016以上的版本才有
第一步【Ctrl A 全选数据】-【数据】-【从表格】创建 Power Query进入 Power Query界面。
第二步添加辅助列。Excel 实现列转换可以通过【添加辅助列】来实现该效果。而在 Power Query 有多种可以添加辅助列的方法。此处介绍两种方法法一通过自定义列添加辅助列法二通过重复列实现添加辅助列
第三步进行透视列。【透视列】【值列自定义选中需要透视的列】-【聚合值函数选择不要聚合】-【确定】。
第四步合并列。选中透视出来的列右键【合并列】-【自定义分隔符】-【确定】 。
最后选中多余的列删除再进行【关闭并上载】。全部搞定列转行后的数据 ps. execl实现的原文链接https://zhuanlan.zhihu.com/p/315340716
2.6 Java 实现行转列
public class Row2Line {public static void main(String[] args) throws IntrospectionException, IllegalArgumentException, IllegalAccessException, InvocationTargetException { //你提供的对象列表需要转换的原数据 ListStudentGrand StudentGrandList getStudentGrandList(); //实现行转列的算法ListListString convertedTable convert(StudentGrandList);//打印转换后的集合查看结果print(convertedTable); //剩下的可以根据实际需求将转换好的集合传给前端、或随意处理} private static ListListString convert(ListStudentGrand StudentGrandList) throws IntrospectionException, IllegalAccessException, InvocationTargetException {//取得StudentGrand的属性当然你也可以用list {id, name, ...} Field[] declaredFields StudentGrand.class.getDeclaredFields();ListListString convertedTable new ArrayListListString(); //多少个属性表示多少行遍历行 for (Field field : declaredFields) {field.setAccessible(true); ArrayListString rowLine new ArrayListString();//listT多少个StudentGrand实体类表示有多少列遍历列for (int i 0, size StudentGrandList.size(); i size; i) {//每一行的第一列对应StudentGrand字段名 //所以新table的第一列要设置为字段名if(i 0){ rowLine.add(field.getName()); } //新table从第二列开始某一列的某个值对应旧table第一列的某个字段else{ StudentGrand StudentGrand StudentGrandList.get(i); String val (String) field.get(StudentGrand);//grand为int会报错System.out.println(val);rowLine.add(val); } } convertedTable.add(rowLine); } return convertedTable; }//测试用数据实际应该从数据库查询传过来的private static ListStudentGrand getStudentGrandList () { ListStudentGrand list new ArrayListStudentGrand();list.add(new StudentGrand(001, toni, 语文, 98));list.add(new StudentGrand(001, toni, 数学, 98));list.add(new StudentGrand(001, toni, 外语, 98));list.add(new StudentGrand(001, toni, 体育, 98));list.add(new StudentGrand(006, amy, 语文, 98));list.add(new StudentGrand(006, amy, 数学, 98));list.add(new StudentGrand(006, amy, 外语, 98));list.add(new StudentGrand(006, amy, 体育, 98));list.add(new StudentGrand(003, 安东尼, 语文, 98));list.add(new StudentGrand(003, 安东尼, 数学, 98));list.add(new StudentGrand(003, 安东尼, 外语, 98));list.add(new StudentGrand(003, 安东尼, 体育, 98));return list; } //打印查看结果private static void print(ListListString convertedTable) {//String json JSONArray.formObject(convertedTable).toString();for (ListString list : convertedTable) { for (String string : list) {System.out.print(string ); } System.out.println(); } } ps. java实现的原文链接https://blog.csdn.net/LRXmrlirixing/article/details/100163550 2.7 hive sql实现行转列
使用函数concat_ws(,,collect_set(column)) 压缩到一行跟题目要求稍有差异。
select user_id,
concat_ws(,,collect_list(order_id)) as order_value
from col_lie
group by user_id
2.8 Teradata UDF实现行转列
还是压缩到一行不如case when的展示效果
REPLACE FUNCTION tdstats.UDFCONCAT(aVarchar VARCHAR(128) CHARACTER SET UNICODE)RETURNS VARCHAR(10000) CHARACTER SET UNICODECLASS AGGREGATE (20000)SPECIFIC udfConcatLANGUAGE CNO SQLNO EXTERNAL DATAPARAMETER STYLE SQLNOT DETERMINISTICCALLED ON NULL INPUTEXTERNAL NAME SL!staudf!F!udf_concatvarchar 三 阑尾
剩下992种包含python、java、C if else实现之类的方法请大家自行脑补欢迎在评论区发表你的高见~ 求点赞、求评论、求收藏
