每天分享一個sql，幫助大家找到sql的快樂
hive sql系列（總結）介紹
hive sql系列主打sql，通過案例，從實作到分析，幫助大家找到寫sql的快樂hive sql系列目錄
1. hive sql系列（一）：找出所有科目成績都大于某一學科平均成績的學生
2. hive sql系列（二）：統計每個用戶每個月訪問量和累計月訪問量
3. hive sql系列（三）：求所有用戶和活躍用戶的總數及平均年齡
4. hive sql系列（四）：請用sql寫出所有用戶中在今年10月份第一次購買商品的金額
5. hive sql系列（五）：一個日志表中記錄了某個商戶費率變化狀態的所有資訊，現在有個需求,要取出按照時間軸順序，發生了狀態變化的資料行
6. hive sql系列（六）：每個用戶連續登錄最大天數
7. hive sql系列（七）：查詢前20%時間的訂單資訊
8. hive sql系列（八）：根據聚合在一起的編碼轉換成聚合在一起的碼值
9. hive sql系列（九）：有一張表，其中一個欄位是由時間、介面、ip和其他欄位組成的
求11月9號下午14點（14-15點），訪問/api/user/login介面的top10的ip地址
10. hive sql系列（十）：撰寫sql陳述句實作每班前三名，分數一樣并列，
同時求出前三名按名次排序的分差需求轉化成sql思路
1、排序相關三個函式：row_number()、rank()、dense_rank()
2、統計A下的B topn，需要基于A開窗根據不同的情況使用排序函式，得到臨時表，然后基于臨時表過濾資料，（重點：開窗、子查詢、需要條件過濾的在子查詢中先過濾）
3、hive sql系列（三）是一個級聯求和的典型例子，意思是當月和累計在一起的意思，以此類推，相似的場景都可以用hive sql系列（三）的方式做，這就形成了一個類似雞兔同籠方式的二元方程式的固定解法
4、寫sql的步驟：分析需求（明確需要做什么） -> 拆解需求（大概如何實作） -> 列出實作步驟（具體實作方式） -> 合并步驟（可以在一步實作的合并，減少子查詢），（有點類似spark、flink算子鏈，算子合并的意思）
5、當遇到實作方式不能得到正確結果時，先核對邏輯，每一步的實作得到的結果是否如你所愿，如果還不能解決，每步一測，確保一進一出時符合的（劃重點）
6、hql通常有兩個場景：一是對業務資料處理，二是對日志資料處理，區別在于：業務資料處理的資料通常是關系型資料，資料比較規整和規矩，不必做過多轉化；日志資料處理的資料通常是埋點資料，資料很長，有格式，需要先做轉化得到符合處理要求時才能進一步統計分析，案例如hive sql（八）（網友的企業實戰）（重點）和hive sql（九）
7、基于開窗排序之上還有取數，那就需要用到lag函式，甚至取數之后還要進行運算，無論多復雜的需求，都可以參考第5點，這讓我想當《演算法》里面說到的一句話——分而治之
8、寫好sql，需要邏輯就可以了，想快點寫好sql，需要多寫sql就好，多次訓練是題海戰術，有題型有固定解法，想又快又好的寫好sql，需要了解不同功能函式并靈活運用，比如日期類函式有多少種，功能是啥，回傳值是啥；又比如排序函式三種的用法等等（重點：這部分的總結下次分享出來，總結中）
9、order by和開窗函式里面的order by重用之痛，可以參考hive sql系列（十）（重點：類似這種會慢慢總結分享出來，如果沒有經驗，會很浪費測驗sql的時間）
10、這次就總結這些了，下次再見，，，歡迎小伙伴們參與進來，享受sql的快樂，知識點
1、over（）開窗范圍：rows是行數的意思，后面跟的是函式的范圍，between是行數在什么范圍，unbounded是行數的起點,這里可以將unbounded替換成1，那就是相鄰上一個月的意思，preceding是前面的意思，current row是當前行的意思
2、cast(資料型別1 as 資料型別2)表示將資料型別1強轉成資料型別2
3、decimal(10,2)是整數位長度為10，小數位長度為2，如果沒有小數位，默認補0
4、row_number：添加序號，無論欄位值是否相同
5、dense_rank()：基于over開窗函式的排序函式，如果值相同，則排序的序號相同，緊接的序號不跳過，舉例123,1123,1223這樣6、6、date_sub(日期，數值)，用日期-數值，即當前日期的前n天，回傳值是日期字串型別
7、ntile：把有序的資料集合平均分配到指定的資料量個桶中，將桶號分配給每一行，如果不能平均分配，則優先分配較小編號的桶，并且各個桶中能放的行數最多相差1，
8、split(字串，分割符)：使用分割符切割字串，回傳一個陣列
9、lateral view explode(陣列)：將陣列欄位拆分成多行
10、concat_ws(連接符,字串，字串)：連接多個字串
11、collect_list(分組鍵)：將分組中的某列聚合成一個陣列，陣列中元素與分組后的資料保持一致
12、map：將多個排列好的k,v,k,v...變成一個map結構，這是初始化map結構的方式，取資料是map[key]
13、row_number()：row_number是基于over()開窗函式的一個不重復的序號，如上結果所示，即便結果相同，也會順延,序號自增
14、substring(字串，起始位置，步長)：根據起始位置和步長切割字串
15、nvl(value1，value2)：如果value1是null，則回傳value2，如果不是，則回傳value1
16、lag(欄位，n，默認值)：基于over開窗函式，根據排序規則取當前行前第n個數，如果不指定n，則默認取前一個，如果取不到，回傳默認值，如果不指定默認值，取不到則回傳Null
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/277824.html
標籤：其他