iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 3
0

Day three
Use MySQL-group by to organise your database.
使用MySQL- group by 來整理資料庫

Hello everyone. My name is Ray! I shared how to import Chinese data into database yesterday, and today I’m going to share how to use MySQL group by to organise your database.
大家好,我是Ray! 昨天跟大家分享如何正確的導入中文的資訊到資料庫裡,今天呢,我將分享如何使用MySQL的group by 來整理資料庫!

Through the image above you could see that the data is divided with different district to each day. Let’s assume that the data of the rightest column is rainfall data, what if we want to access the average rainfall of each day from all districts?
We could use MySQL-group by to achieve it.
在上圖我們可以看到,所有資料都以不同的地區來做劃分。假設右手邊的欄位資料爲降雨量好了,如果我們今天想要取得全地區的平均降雨量,該怎麼作呢?
我們可以使用MySQL的group by 來達到我們的目的。

Take a look on the snippet of code below:
輸入以下code:

select date, avg(rainfall) rainfall from rainfall group by date;

The first date in the above code means name of the column, avg means to average the amount inside the braces, which means to average the amount of value on the rainfall column.
The second rainfall is the name of this table, and the final date means that we reorganise the data with date as its unit. It would enable automatic calculation of averaging on the data on the same date.
上面的date代表我日期欄位的名稱,avg代表平均值,rainfall代表降雨量欄位的名稱,而在括號後面又出現一次rainfall代表顯示在取得的資料表上的欄位的名稱,最後一個rainfall則是我這個表格的名稱。
由於我select的項目裡並沒有地區,而最後的group by 表示資料將以date下去做重新整理,如果有相同天數的欄位就會自
動重整,並使用我前面下的avg平均化處理。

The result is as follows:
得出的結果如下圖:


Here you go! Above mentioned is my sharing today. Good luck to all of you, and have a good coding day!!
以上便是我今天的分享,感謝各位!我們明天見!


上一篇
如何正確的導入中文到MySQL資料庫而不會出現亂碼?
下一篇
使用MySQL- group by 來整理資料庫~2
系列文
Experience of a backend novice30

尚未有邦友留言

立即登入留言