mysql列转行

背景

• 需要将原子数据表的数据同步到结果表

• 原子数据表：base_index

  date_time	index_id	value
  2023-01	pv	100
  2023-01	uv	200
  2023-01	newUser	300
  2023-02	pv	400
  2023-02	uv	500
  2023-02	newUser	600

• 结果表：result_index

  date_time	pv	uv	newUser
  2023-01	100	200	300
  2023-02	400	500	600

思路

通过联表

• 将每种index_id筛选出来为一个子表，然后通过对date_time left join的方式，拼接成宽表。这种方式得保证驱动表的时间是完整的，如驱动表的index_id为pv，但是pv只有2023-01没有2023-02，但是uv有2023-02，此时数据有缺失。所以此方案行不通。

grouy by

• group by是先执行非group by部分的select，然后按照group by的字段分组，如果没有聚合函数，往往最后保留每组的第一条数据返回到结果集

• 按date_time聚合，然后通过if将不同index_id分别判断，目的扩展成多个不同字段

• 使用max()聚合函数，等于将每组中每列的最大值提到返回的这条数据中来

  select 
  	max(if(index_id = 'pv', `value`, null)) as pv,
  	max(if(index_id = 'uv', `value`, null)) as uv,
  	max(if(index_id = 'newUser', `value`, null)) as newUser,
  	date_time
  	from base_index
  	group by date_time
  

参考

• mysql 行转列 列转行
• MySQL中的行转列和列转行
• mysql 列转行，合并字段