【SQL】sql问题记录

• SQL中字符串截取函数(SUBSTRING)

我要取该字段中前10个编号

SELECT
	pid,
	substring_index(similar_skus, '#', 10) AS top10
FROM
	table
where dt = sysdate(-1)

•  sql取前10条记录

select * from table where 1=1 limit 10

• split

SELECT
    split(id,'_')[1] as id,
    substring_index(similar, '#', 10) AS simi_top10

结果由变成，取了lsh后半部分。

 

Hive之列转行,行转列

hive 行转列 https://www.it610.com/article/4964744.htm

SELECT
	split(main_sku_id, '_') [1] AS main_sku_id,
	simi_top10
FROM
	app.app_gensim_word2vec_sim_sku_for_jimdb lateral VIEW explode(split(substring_index(similar_skus, '#', 10),'#')) similar_skus as simi_top10
WHERE
	dt = sysdate( - 2)

原：

现：

 

 

行转列

SELECT
	split(main_sku_id, '_') [1] AS main_sku_id,
	simi_top10
FROM
	app.app_gensim_word2vec_sim_sku_for_jimdb lateral VIEW explode(split(substring_index(similar_skus, '#', 10),'#')) similar_skus as simi_top10
WHERE
	dt = sysdate( - 2)

 

when case 

HIVE行转列和列转行

 

一个sql博主：https://blog.csdn.net/rongtaoup/category_7982553.html