• <noscript id="e0iig"><kbd id="e0iig"></kbd></noscript>
  • <td id="e0iig"></td>
  • <option id="e0iig"></option>
  • <noscript id="e0iig"><source id="e0iig"></source></noscript>
  • MySQL的行轉列與列轉行

    標簽: mysql  hadoop

    行轉列與列轉行的概念

    這里需要重申一下行轉列和列轉行的區別。有很多的貼子在介紹的時候沒有嚴格的區分,命名介紹的是列轉行該如何操作,但是帖子的標題確寫的是行轉列。自始至終都沒有提過列轉行的事情。所以,我覺的這里應該有必要做一次區別和認識。

    什么是行轉列

    所謂的行轉列是指把數據表中具有相同key值的多行value數據,轉換為使用一個key值的多列數據,使每一行數據中,一個key對應多個value。

    行轉列完成后,在視覺上的效果就是:表中的總行數減少了,但是列數增加了。

    如下所示的轉換過程就是一個簡單的行轉列的過程:
    在這里插入圖片描述

    什么是列轉行

    所謂的列轉行是指把表中同一個key值對應的多個value列,轉換為多行數據,使每一行數據中,保證一個key只對應一個value。

    列轉行完成之后,在視覺上的效果就是:表中的列數減少了,但是行數增加了。

    如下所示的轉換過程就是一個簡單的列轉行過程:
    在這里插入圖片描述

    行轉列實驗示例

    在進行實驗之前,我們需要先準備好我們的實驗環境,準備好表和表中的初始化數據。

    • 準備初始化表結構
    CREATE TABLE `student_x` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `class` varchar(255) DEFAULT NULL,
      `score` int(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    • 準備初始化表中的數據
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (1, '張三', '數學', 78);
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (2, '張三', '英語', 93);
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (3, '張三', '語文', 65);
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (4, '李四', '數學', 87);
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (5, '李四', '英語', 90);
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (6, '李四', '語文', 76);
    INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (7, '李四', '歷史', 69);
    
    • 進行行轉列的實驗環境最后如下所示:
      在這里插入圖片描述

    示例:使用聚合函數

    我們在這個示例中,使用聚合函數sum、max、min、avg來完成我們的行轉列的需求,具體的實驗如下。

    • 下面是我們需要完成行轉列的效果圖:
      在這里插入圖片描述
    • 使用case when語句來拼裝新的數據列
    select name,
    	case when class = '數學' then score else null end as math_score,
    	case when class = '英語' then score else null end as engilsh_score,
    	case when class = '語文' then score else null end as chinese_score,
    	case when class = '歷史' then score else null end as history_score
    from student_x;
    

    在這里插入圖片描述

    • 基于上面的效果圖,我們需要把數據結果,按照name列進行聚合,讓姓名相同的數據行合并為同一行來展示,同時,每一列的科目中,只有一行數據是有成績的,其他行成績都是空null,所以他們結合使用max函數,可以達到合并行,并且每列的科目成績不會因為合并行而影響到最后的每一科目的成績。實現上述轉換的SQL語句如下:
    select name,
    	max(case when class = '數學' then score else null end) as math_score,
    	max(case when class = '英語' then score else null end) as engilsh_score,
    	max(case when class = '語文' then score else null end) as chinese_score,
    	max(case when class = '歷史' then score else null end) as history_score
    from student_x
    group by name;
    

    在這里插入圖片描述

    • 我們上面使用了case when語句來判斷,其實if語句也可以達到case when語句的效果。如下是使用if語句的結果:
    select name,
    	max(if(class = '數學', score, null)) as math_score,
    	max(if(class = '英語', score, null)) as engilsh_score,
    	max(if(class = '語文', score, null)) as chinese_score,
    	max(if(class = '歷史', score, null)) as history_score
    from student_x
    group by name;
    

    在這里插入圖片描述

    • 實現過程分析
    1. 這里我們使用了聚合函數max,把每一個學生的姓名作為key,進行分組統計。
    2. 因為每一個學生對應每一門科目的成績只有一行記錄,所以我們使用聚合函數sum統計后的每一科目的成績,仍然是該科目單獨的成績。
    3. 如果這里每一個學生對應每一門科目有多個成績記錄,這里就不能使用聚合函數max了,如果使用max,最后的結果將是每一個學生對應每一門科目成績的最大值。
    4. 這里之所以使用max的目的是為了達到一個分組的效果。這里的max可以使用sum、min、avg等聚合函數替換掉,它們三個的效果和sum函數的效果在這里是一樣的。

    總結:上面的這樣的實現方式,使大家經常使用的,也是大家最熟悉的一種方式。但是這樣的寫法有一個問題,就是當我們的科目名稱變動或者增加或者減少的時候,我們SQL語句也需要作出對應的修改。因為我們在SQL語句中已經使用了hard code硬編碼的方式把科目的名稱給寫死了,所以這樣的SQL不太靈活。

    我們可以參考使用下面的幾種寫法,每一種寫法稍微有點不同,但是這些方式基本都能滿足我們的需求。

    列轉行實驗示例

    在進行列轉行的實驗之前,我們需要先準備好我們的實驗環境,準備好表和表中的初始化數據。表結構和初始化數據如下:

    • 準備初始化表結構
    CREATE TABLE `student_y` (
      `id` int(11) DEFAULT NULL, 
      `name` varchar(255) DEFAULT NULL,
      `math_score` bigint(255) DEFAULT NULL,
      `engilsh_score` bigint(255) DEFAULT NULL,
      `chinese_score` bigint(255) DEFAULT NULL,
      `history_score` bigint(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    • 準備初始化表中的數據
    INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (1, '張三', 78, 93, 65, NULL);
    INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (2, '李四', 87, 90, 76, 69);
    
    • 進行列轉行的實驗環境最后如下所示:
      在這里插入圖片描述

    示例:使用union all功能

    實驗環境準備后之后,接下來我們開始使用union all的功能來實現列轉行的功能。

    • 下面是我們需要完成行轉列的效果圖: 在這里插入圖片描述
    • 我們可以對所有學生的每一個科目的成績進行單獨查詢,結果如下:
    select name, math_score as score from student_y;
    select name, engilsh_score as score from student_y;
    select name, chinese_score as score from student_y;
    select name, history_score as score from student_y;
    

    在這里插入圖片描述
    在這里插入圖片描述
    在這里插入圖片描述
    在這里插入圖片描述

    • 基于上面的每一個查詢結果,把最后的結果使用union all關鍵詞合并在一起,效果如下:
    select name, math_score as score from student_y
    union all
    select name, engilsh_score as score from student_y
    union all
    select name, chinese_score as score from student_y
    union all
    select name, history_score as score from student_y;
    

    在這里插入圖片描述

    • 此時,我們發現結果中已經大概實現了列轉為行的需求。但是順序沒有達到要求,每個人的各個科目的成績應該挨著,但是目前是沒有挨著的。所以我們需要在基于上面的查詢結果,外面在包裹一層查詢,增加一個order by語句在外層查詢中,即可得到我們想要的順序。如下所示:
    select * from (
    	select name, math_score as score from student_y
    	union all
    	select name, engilsh_score as score from student_y
    	union all
    	select name, chinese_score as score from student_y
    	union all
    	select name, history_score as score from student_y
    ) as x order by name;
    

    在這里插入圖片描述

    • 此時的結果已經很接近我們的最后想要的結果了,但是我們發現,每個學生的成績我們不能區分各個科目的成績是多少,所以我們需要把科目也納入到結果集中,也就是我們在查詢的時候,要把各個成績對應的列名稱也包含到查詢的字段中。于是就有了如下的SQL
    select * from (
    	select name, 'math_score' as class, math_score as score from student_y
    	union all
    	select name, 'engilsh_score' as class, engilsh_score as score from student_y
    	union all
    	select name, 'chinese_score' as class, chinese_score as score from student_y
    	union all
    	select name, 'history_score' as class, history_score as score from student_y
    ) as x order by name,class;
    

    在這里插入圖片描述
    總結:這里我們采用了union all的功能,把所有學生每個科目的成績單獨查詢出來,然后把結果集繼續合并。最后達到我們想要的列轉為行的效果。但是我們發現此時的SQL語句中,和前面我們在進行行轉列的時候,使用聚合函數的方式來實現行轉列的方式類似,使用了hard code的硬編碼,如果科目名稱或數目發生改變,我們的SQL語句也需要跟著動態的去修改,這是這種方式的缺陷。但是優點就是比較容易理解。

    最后總結

    我們在文章的開始,首選針對行和列相互轉行的概念做了簡單澄清,因為有很多人會把行轉列和列轉行給混淆。行轉列和列轉行的最明顯的區分如下:

    行轉列,最后的結果中行變少了,列變多了。
    列轉行,最后的結果中列變少了,行變多了。
    緊接著,我們分別針對行轉列和列轉行進行了實驗演示。

    版權聲明:本文為zhao_guo_feng原創文章,遵循 CC 4.0 BY-SA 版權協議,轉載請附上原文出處鏈接和本聲明。
    本文鏈接:https://blog.csdn.net/zhao_guo_feng/article/details/110951392

    智能推薦

    神奇的Batch Normalization 如果一個模型僅訓練BN層會是什么樣的

    您可能會感到驚訝,但這是有效的。 ? 最近,我閱讀了arXiv平臺上的Jonathan Frankle,David J. Schwab和Ari S. Morcos撰寫的論文“Training BatchNorm and Only BatchNorm: On the Expressive Power of Random Features in CNNs”。 這個主意立刻引起了...

    用Python實現校園通知更新提醒

    前言 這個項目實已經在一個月前已經完成了,一直都想寫一篇博客來總結這個過程中遇到的一些問題。但最近一個月來都比較忙,所以一直拖到了現在。 首先說說起因吧,我沒事的時候,總喜歡依次點開學校主頁、教務處、圖書館以及學院的網站,看看有沒有什么新通知,雖然大多與我無關。恰逢最近正在學Python,經常聽到別人說用Python寫爬蟲很簡單,但自己尚未接觸過爬蟲。于是抱著試一試的心態看了幾篇關于Python爬...

    spring_ioc相關_第一章

    1 spring是一站式框架,在javaee的三層結構中,每一層都提供不提并的解決技術 web層:springMVC service層:spring的ioc dao層:spring的jdbcTemplate 2 javaee為避免兩個類之間出現耦合,則把對象的創建交給spring進行管理,spring的ioc操作:(1)ioc的配置文件方式;(2)ioc注解方式 3 ioc的底層原理使用技術(1)...

    【Python+OpenCV】視頻流局部區域像素值處理-一種特征提取方法

    參考我之前寫的處理圖片的文章:Python+OpenCV實現【圖片】局部區域像素值處理(改進版) 開發環境:Python3.6.0 + OpenCV3.2.0 任務目標:攝像頭采集圖像(例如:480*640),并對視頻流每一幀(灰度圖)特定矩形區域(480*30)像素值進行行求和,得到一個480*1的數組,用這480個數據繪制條形圖,即在逐幀采集視頻流并處理后“實時”顯示采...

    JavaWeb——【前端】——注冊頁面

    頁面效果 實現代碼 注意事項 主要使用的bootstrap樣式 如果想引用,不要直接復制,沒用的。 先介紹下所引用的文件: boostrap的js、bootstrap的css、jquery的js、以及自己編寫的register.css。 因為博主用的thymeleaf語法,所以有th符號。 若要使用時,根據個人情況導入相應的依賴。...

    猜你喜歡

    網站HTTP升級HTTPS完全配置手冊

    本文由葡萄城技術團隊于博客園原創并首發 轉載請注明出處:葡萄城官網,葡萄城為開發者提供專業的開發工具、解決方案和服務,賦能開發者。 今天,所有使用Google Chrome穩定版的用戶迎來了v68正式版首個版本的發布,詳細版本號為v68.0.3440.75,上一個正式版v67.0.3396.99發布于6月13日,自Chrome 68起,當在加載非HTTPS站點時,都會在地址欄上明確標記為&ldqu...

    echarts 自定義儀表盤設置背景圖片

    echarts儀表盤 使用插件 vue-echarts 代碼示例 HTML部分 js部分 效果圖...

    RT-Thread Studio部分定時器時鐘不正確的解決方案

    在昨天的RT-Thread Studio硬件定時器hwtimer在stm32f411上的使用筆記中,遇到了部分定時器速度想象中和實際不一致的情況,具體表現在定時器2、3、4、5、9、10、11都正常,但定時器1要快一倍。 仔細查看代碼,找到了原因。 因為代碼使用的是工程是直接生成的時鐘代碼,實際的時鐘頻率是這樣的: 而實際的定時器時鐘配置代碼如下: 針對F411,去掉其中的宏定義是這樣的: 這里說...

    symfony學習筆記之模板渲染-----twig總結

    參考:https://blog.csdn.net/liebert/article/details/77414217 目錄 一、模板引擎工作原理 二、Twig模板引擎 1.運行環境要求 2.基本API用法 3.設計模板 (1)變量輸出         a.全局變量         b.設置變量 (2)...

    小甲魚Python3學習筆記之第六講(僅記錄學習)

    第六講:python之常用操作符 一、知識點: 0.算術運算符:+,-,*,/,%(取模,即求余數),**(冪運算),//(地板除法,取整除,返回商的整數部分) 備注:①雙斜杠 // 除法總是向下取整。             ②從符點數到整數的轉換可能會舍入也可能截斷,建議使用math....

    精品国产乱码久久久久久蜜桃不卡