hive 列表去重_hive小结 - 校招准备

行转列:explode

列转行:

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是stringhive 基础知识的总结回顾 内含我收集的几个sql题目, 用来回顾练手的

包括一些优化相关的总结

是什么

• 为Hadoop上的任务提供一个sql的接口
• hive适用于数据仓库应用程序 OLAP 任务
  • 静态数据分析
  • 对实时性要求不高

• 不是一个完整的数据库
  • 只有表的元数据
  • 具体数据存储在HDFS中

数据模型

• db
  • 在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹

• table
  • 在hdfs中表现所属db目录下一个文件夹
• external table
  • 与table类似，不过其数据存放位置可以在任意指定路径(外部表)

• partition
  • 在hdfs中表现为table目录下的子目录
• bucket
  • 在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

基本组成

• 用户接口
  • CLI
  • JDBC/ODBC
  • WebGUI

• 元数据存储
• 解释器、编译器、优化器、执行器
  • 完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成

hive服务

• cli
  • 命令行接口
  • hive
    • -- 注释
    • -d 定义kv值
    • -f filename
    • -h help
    • -h hostname
    • -e 一次性的命令
      • hive -e "" > file

• hiveserver2
  • 让hive以thrift服务运行
• beeline
  • 嵌入式工作的hive命令行接口

• hwi
  • hive的web接口
• hue
  • 更加全面的web接口

• metastore
  • 默认metostore和hive在同一个服务里
  • 可以让其成为单独的元数据服务

特点

• 内部表 外部表
• 存储格式
  • 文件
  • 二进制格式

• 元数据
  • 单机
  • 元数据服务
    • 若配置了,每次都要启动
    • hive --service metastore &
• 视图
  • 类似关系型数据库视图

• 索引
  • Hive的索引目的是提高Hive表指定列的查询速度。
• 执行引擎
  • mapreduce
  • tez

• 读时模式 写时模式
  • hive是读时模式
  • 在加载的时候验证

分区与分桶

分区

• 每个分区建立一个文件夹
• 外部分区表
• 自己增加分区
• 自己挂载进来

分桶

• 桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，并使每个桶对应于该列名下的一个存储文件。
• 随机分数据

hql语法

基础

• 数据类型
  • 基本数据类型
  • String

• 集合数据类型
• hdfs支持的文件压缩格式

数据库操作

• 与mysql类似
• 可以用like
  • show databases like "h.*"

• if not exists

表操作

• describe table
  • 查勘表的情况

• show partitions XXX
  • 显示XXX的分区情况
• create teble XXX like XXX

建表

建表语法 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]

说明： 1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。 2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。 3、LIKE 允许用户复制现有的表结构，但是不复制数据。 4、ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。 5、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY 对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

• create table
• (name: type, ..)
  • 属性

• ROW FORMAT DELIMITED
  • 指定下面的分隔符
  • 要先有这个
• FIELDS TERMINATED By '001'
  • 每列之间分割

• COLLECTION ITEMS TERMINATED by '002'
  • list之间
• MAP Keys TERMINATED by '003'
  • map之间

• Lines TERMINATED by 'n'
  • 不需要粉色的 声明开头
  • 每行之间
• PARTITIONED by (year int,day int)
  • 划分分区

• CLUSTERED BY(userid) SORTED BY(viewTime) INTO n BUCKETS
• STORED as
  • 存储的文件格式
  • 默认 TEXTFILE
  • SEQUENCEFILE
  • RECFILE
  • 还可以 自己制定 InputFormat 和OutputFormat
• LOCATION ''"
  • 存储在哪里
  • 默认是 /user/hive/warehouse

查询

• select中 可以使用 array下标和 map[key]
• case when then
  • if语句
  • CASE when then when then

• group having
• join
  • inner
  • left
  • outer
  • join优化
    • mapside join
• sort by order by
• distribute by
  • 控制reducer如何划分
  • 可以和sort by一起用

• limit

函数类型

• 数学函数
  • round
    • 近似值

• • abs
• 聚合函数
  • count
  • max min avg sum
  • hive.map.aggr

• 生成函数
  • explode
    • 返回多个元素
  • stack
    • m列转为n行

• 其他
  • cast( as )
    • 类型装换
  • length
  • split
  • ltrim
  • lower
  • repeat
    • 重复n次

数据操作

• load data [ local] inpath "XXX" [overwrite] into table XXX [Partition (...)]

2.2.1Load 语法结构 LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明： 1、Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。 2、filepath： 相对路径，例如：project/data1 绝对路径，例如：/user/hive/project/data1 包含模式的完整 URI，列如： hdfs://namenode:9000/user/hive/project/data1 3、LOCAL关键字 如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。 如果没有指定 LOCAL 关键字，则根据inpath中的uri[如果指定了 LOCAL，那么： load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。 load 命令会将 filepath中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。

如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。 否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。 如果路径不是绝对的，Hive 相对于/user/进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。]查找文件

4、OVERWRITE 关键字 如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。 如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

- 没有local的话 就是在hdfs中, 会将其移动过去

• insert [overwrite] table XXX Partition(...) select *

将查询结果插入Hive表 语法结构 利用查询语句，将查询结果插入新的表 INSERT OVERWRITE [INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

插入一条数据 INSERT INTO TABLE VALUES(XX,YY,ZZ);

Multi Inserts多重插入: FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] Select statement2] ...

Dynamic partition inserts动态分区插入: INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

- 可以动态分区插入
- partition里面是 select里面的列

• create table user_13302 as Select * from posts

修改

• 增加修改 删除 表分区
  • alter table xxx add partition ...
  • alter table xxx drop partition ...
  • alter table xxx partition ... set ...

• 修改表
  • Alter table xxx Change Column
  • Alter table xxx add Column

应用

DEMO

• hive版 wordcount
• 给User表分区
  • 原始数据
  • 导入分区后的表中

模式设计

• 如何分区

调优

• explain
  • 显示语法树

参数与优化

• set hive.auto.convert.join= true
  • map端join
  • innor和left

• 本地模式
  • 本地模式执行
• 设置reducers task的最大个数
  • set hive.exec.reducers.max=

• 设置reduce task的任务数
  • set mapred.reduce.tasks=
• 每个reducer task任务处理的数据量。默认为1G
  • set hive.exec.reducers.bytes.per.reducer=

• jvm重用
• map端 join
  • hive.vectorized.execution.mapjoin.native.enabled=true;
• hive.vectorized.execution.mapjoin.native.fast.hashtable.enabled=true;
• hive.vectorized.execution.mapjoin.minmax.enabled=true;
• hive.vectorized.execution.reduce.enabled=true;
• hive.llap.client.consistent.splits=true;
• hive.optimize.dynamic.partition.hashjoin=true;

原理

文件的存储

支持的数据类型

hdfs支持的文本压缩

- gzip压缩

    - 压缩率比较高，而且压缩/解压速度也比较快
    - 缺点：不支持split
    - 应用场景

        - 当每个文件压缩之后在130M以内的（1个块大小内），都可以考虑用gzip压缩格式

- lzo压缩

    - 优点

        - 压缩/解压速度也比较快，合理的压缩率
        - 支持split，是hadoop中最流行的压缩格式

    - 压缩率比gzip要低一些；hadoop本身不支持，需要安装
    - 应用场景：一个很大的文本文件，压缩之后还大于200M以上的可以考虑，而且单个文件越大，lzo优点越越明显

- snappy

    - 优点：高速压缩速度和合理的压缩率；支持hadoop native库。
    - 缺点：不支持split；压缩率比gzip要低
    - 应用场景

        - 当mapreduce作业的map输出的数据比较大的时候，作为map到reduce的中间数据的压缩格式
        - 或者作为一个mapreduce作业的输出和另外一个mapreduce作业的输入。

- bzip2

    - 优点

        - 支持split
        - 具有很高的压缩率，比gzip压缩率都高

    - 缺点：压缩/解压速度慢；不支持native。
    - 应用场景

        - 适合对速度要求不高，但需要较高的压缩率的时候
        - 可以作为mapreduce作业的输出格式
        - 或者输出之后的数据比较大，处理之后的数据 需要压缩存档减少磁盘空间并且以后数据用得比较少的情况
        - 或者对单个很大的文本文件想压缩减少存储空间，同时又需要支持split，而且兼容之前的应用程 序（即应用程序不需要修改）的情况

orc

- ORC是在一定程度上扩展了RCFile，是对RCFile的优化
- orc.compress ZLIB  high level compression (one of NONE, ZLIB, SNAPPY)
    - 结构

        - ORCFile在RCFile基础上引申出来Stripe和Footer
        - 每个ORC文件首先会被横向切分成多个Stripe，而每个Stripe内部以列存储
        - IndexData中保存了该stripe上数据的位置信息，总行数等信息
        - RowData以stream的形式保存了数据的具体信息
        - Stripe Footer中包含该stripe的统计结果，包括Max，Min，count等信息

    - Hive读取数据的时候，根据FileFooter读出Stripe的信息，根据IndexData读出数据的偏移量从而读取出数据
    - 压缩

        - ZLIB

    - 支持的引擎

        - hive

- parquet

    - Parquet文件是以二进制方式存储的，是不可以直接读取和修改的
    - Parquet文件是自解析的，文件中包括该文件的数据和元数据
    - 压缩

        - snappy
        - gzip

    - 支持的引擎

        - drill
        - impala
        - hive

- avro

    - CREATE TABLE kst
      PARTITIONED BY (ds string)
      ROW FORMAT SERDE
      'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
      STORED AS INPUTFORMAT
      'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
      OUTPUTFORMAT
      'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
      TBLPROPERTIES (
        'avro.schema.url'='http://schema_provider/kst.avsc')

Sequencefile

sql命令对应MR

select , where

• map中进行

join

• 类型
  • map端
    • 属于一个优化,在小表可以装在内存中时使用

• • reduce端
    • 类似MRreduce join
• 原理
  • map
    • 以join的key作为map输出的key
    • 区分两个tag
    • where
      • 可以在语法树中下拉
      • 提前过滤

• • shuffle
    • 根据 Key 的值进行 Hash，并将 Key/Value对按照 Hash值推至不同对Reduce中
  • reduce
    • 按照key join
    • 通过tag标识不同数据

grouy by

• 按照key划分reduce
• reduce中执行聚合操作

distinct

• 把整个记录都作为map的输入
• 在reduce上去重

sort by order by

利用reduce排序的特性

高级特性

文件格式

• SerDe
  • 序列化/反序列化简写
  • avro
  • hadoop 的InputFormat
    • 最基本的

• sequence file
  • 一种HDFS上的容器
  • stored as sequencefile
  • 支持三种压缩
    • None
    • Record
    • block
• RCFile
• ORCFile
• 自定义输出

自定义函数

UDF

• 自定义或内置的函数
• 相当于 map
• 如何使用
  • 1、先开发一个java类，继承UDF，并重载evaluate方法
  • 2、打成jar包上传到服务器
  • 3、将jar包添加到hive的classpath
    • hive>add JAR /home/hadoop/udf.jar;

• • 4、创建临时函数与开发好的java class关联
    • Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';
  • 5、即可在hql中使用自定义的函数tolowercase ip
    • Select tolowercase(name),age from t_test;

• 例子 字符串转化

UDAF

• 用户定义聚合函数
• 类似count
• 例子

UDTF

• 表生成函数
• explode
  • 1变多

• array
  • 多变1
• 例子 行转列

锁和事务

• 锁
  • 配合zookeeper使用
  • 共享锁
  • 独占锁
  • 可以显式定义锁

• hive2 ACID特性

Thrift服务

• 通过端口远程访问hive
• 元数据存储服务

HCatalog

• 屏蔽了底层数据存储的位置格式等信息，为上层计算处理流程提供统一的，共享的metadata
• 基于 Hive metastore

Streaming

• hive语句中嵌入流处理代码
• 比UDF效率更低

视图

• 类似关系型数据库

索引

• 类似关系型数据库,单有区别
• 建立索引还是可以提高Hive表指定列的查询速度
• 但是好像没什么卵用
• 索引不会自动更新
• 索引可以自己定制

开窗函数

用法

• 函数
• over
• ( partition by order by)

sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1, sum(pv) over (partition by cookieid order by createtime) as pv2, sum(pv) over (partition by cookieid) as pv3, sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4, sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5, sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6

如果不指定ROWS BETWEEN,默认为从起点到当前行; 如果不指定ORDER BY，则将分组内所有值累加; 关键是理解ROWS BETWEEN含义,也叫做WINDOW子句： PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：起点，

• UNBOUNDED PRECEDING 表示从前面的起点， 　　
• UNBOUNDED FOLLOWING：表示到后面的终点

普通聚合

• sum
• max
• avg
• min

高级函数

• ntile
  • 分片
  • 用于将分组数据按照顺序切分成n片，返回当前切片值

• rank()
• ROW_NUMBER
  • 行号
  • 按顺序编号，不留空位
• RANK()
  • 生成数据项在分组中的排名，排名相等会在名次中留下空位

• DENSE_RANK()
  • 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
• LAG(col,n,DEFAULT)
  • 用于统计窗口内往上第n行值
  • 第一个参数为列名，
  • 第二个参数为往上第n行（可选，默认为1），
  • 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

• LEAG
  • LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
  • 第一个参数为列名，
  • 第二个参数为往下第n行（可选，默认为1），
  • 第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
• FIRST_VALUE
• LAST_VALUE

grouping sets

• 在一个GROUP BY查询中，根据不同的维度组合进行聚合
• 等价于将不同维度的GROUP BY结果集进行UNION ALL

GROUPING__ID，表示结果属于哪一个分组集合

WITH CUBE

• 根据GROUP BY的维度的所有组合进行聚合
• 等于Grouping set(所有的)
• SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPINGID FROM cookie5 GROUP BY month,day WITH CUBE ORDER BY GROUPINGID;

ROLLUP

• 是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合
• SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPINGID FROM cookie5 GROUP BY month,day WITH ROLLUP ORDER BY GROUPINGID;

RCfile

先水平划分，再垂直划分的理念

首先将表分为几个行组，对每个行组内的数据进行按列存储，每一列的数据都是分开存储

优化补充

行转列:explode

列转行:

使用函数：concat_ws(',',collect_set(column))

说明：collect_list 不去重，collect_set 去重。 column的数据类型要求是string

数据倾斜优化:

group by

我使用Hive对数据做一些类型统计的时候遇到过某种类型的数据量特别多，而其他类型数据的数据量特别少。当按照类型进行group by的时候，会将相同的group by字段的reduce任务需要的数据拉取到同一个节点进行聚合，而当其中每一组的数据量过大时，会出现其他组的计算已经完成而这里还没计算完成，其他节点的一直等待这个节点的任务执行完成，所以会看到一直map 100% reduce 99%的情况。

解决方法：set hive.map.aggr=true　　 　　　　　set hive.groupby.skewindata=true　　　 　　　　　set hive.groupby.mapaggr.checkintenval=1000000; 这个是group的键对应的记录条数超过这个值则会进行优化　　 原理：hive.map.aggr=true 这个配置项代表是否在map端进行聚合　　　　　　　　　　　 　　　　　hive.groupby.skwindata=true 当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

map和reduce优化。

1.当出现小文件过多，需要合并小文件。可以通过set hive.merge.mapfiles=true来解决。 2.单个文件大小稍稍大于配置的block块的大写，此时需要适当增加map的个数。解决方法：set mapred.map.tasks个数 3.文件大小适中，但map端计算量非常大，如select id,count(*),sum(case when...),sum(case when...)...需要增加map个数。解决方法：set mapred.map.tasks个数，set mapred.reduce.tasks个数　

count

当HiveQL中包含count（distinct）时, 如果数据量非常大，执行如select a,count(distinct b) from t group by a;类型的SQL时，会出现数据倾斜的问题。 解决方法：使用sum...group by代替。如select a,sum(1) from (select a, b from t group by a,b) group by a;

当遇到一个大表和一个小表进行join操作时。

解决方法：使用mapjoin 将小表加载到内存中。 当表小于25mb的时候，小表自动注入内存 set hive.auto.convert.join=true; set hive.mapjoin.smalltable.filesize=25mb; 默认值是25mb

join优化

两阶段处理 set hive.optimize.skewjoin=true;如果是join过程出现倾斜 应该设置为true set hive.skewjoin.key=1000000;--这个是join的键对应的记录条数超过这个值则会进行优化

排序选择

cluster by：对同一字段分桶并排序，不能和 sort by 连用 distribute by + sort by：分桶，保证同一字段值只存在一个结果文件当中，结合 sort by 保证 每个 reduceTask 结果有序 sort by：单机排序，单个 reduce 结果有序 order by：全局排序，缺陷是只能使用一个 reduce

小文件合并

小文件合并 文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的 结果文件来消除这样的影响：

set hive.merge.mapfiles = true ##在 map only 的任务结束时合并小文件

set hive.merge.mapredfiles = false ## true 时在 MapReduce 的任务结束时合并小文件

set hive.merge.size.per.task = 25610001000 ##合并文件的大小

set mapred.max.split.size=256000000; ##每个 Map 最大分割大小

set mapred.min.split.size.per.node=1; ##一个节点上 split 的最少值

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行 Map 前进行小文件合并

Hive的几个面试题

这几个面试题是手生的时候的,用来回顾的题目 https://www.cnblogs.com/qingyunzong/p/8747656.html

求单月访问次数和总访问次数

现要求出：每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数 用户名uname，月份umonth，访问次数ucount A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,16 A,2015-03,22 B,2015-03,23 B,2015-03,10 B,2015-03,1

create external table if not exists t_access( uname string comment '用户名', umonth string comment '月份', ucount int comment '访问次数' ) comment '用户访问表' row format delimited fields terminated by ","

结果数据格式如下

当前月的访问次数

select uname, umonth, count(*) from table group by uname, umonth

累计到本月的总访问次数

select uname, umonth sum(ucount) over(partition by uname order by uname, umonth rows between unbounded preceding and current row)

累积到本月的最大月访问次数

select uname, umonth max(ucount) over(partition by uname order by uname, umonth rows between unbounded preceding and current row)

一行命令搞定

select
uname,umonth , 
max(sum_count) over(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), total_count,sum_count #
from
(select uname,umonth ,max(total_sum) as total_count ,sum(ucount) as sum_count #求出当前用户,当前月的访问次数
    from
        (select uname,umonth,ucount, 
        sum(ucount) over(partition by uname order by uname,umonth  #最内层求出累计到本月的总访问次数
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
        as total_sum 
        from t1
        ) as tmp1
    group by uname,umonth
    ) as tmp2 order by uname,umonth;

所有数学课程成绩 大于 语文课程成绩的学生的学号

用sql求出所有数学课程成绩 大于 语文课程成绩的学生的学号

CREATE TABLE `course` (
`id` int,
`sid` int ,
`course` string,
`score` int 
) ;

// 字段解释：id, 学号， 课程， 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

自连接

select a.sid
from course a join course b on a.sid = b.sid and a.course = 'shuxue' and b.course ='yuwen'
where a.score > b.score;

使用case...when...将不同的课程名称转换成不同的列

create view tmp_course_view as
select sid, case course when "shuxue" then score else 0 end  as shuxue,  
case course when "yuwen" then score else 0 end  as yuwen from course;

create view tmp_course_view1 as
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid;

select * from tmp_course_view1 where shuxue > yuwen;

求学生选课情况

求出id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。

create table t_course(id int,course string) row format delimited fields terminated by ",";

id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e

select id, 
if(find_in_set('a',courses)==0,0,1) as a,
if(find_in_set('b',courses)==0,0,1) as b,
if(find_in_set('c',courses)==0,0,1) as c,
if(find_in_set('d',courses)==0,0,1) as d,
if(find_in_set('e',courses)==0,0,1) as e
from 
(
select id,concat_ws(',',collect_list(cid)) as courses 
from t3
group by id) tmp;