Try   HackMD

資料庫期末專題

TOC

資料庫伺服器處理

開啟access control
stackoverflow 解釋
原因出自於origin=null(從本地資料夾直接開啟),而host=localhost
這個限制出自於browser的設定
需要在chrome 的捷徑設定裡面加上 --disable-web-security 把 CORS 關閉

Access to XMLHttpRequest at 'http://localhost/server_side_script.php' from origin 'null' has been blocked by CORS policy: Request header field access-control-allow-origin is not allowed by Access-Control-Allow-Headers in preflight response.

Router 處理

進到 Router 內設定,找 port forwording 或是 virtual server
remote port 輸入和 local port 相同,local IP 輸入這台機器從 Router 上看到的 IP (通常為192.168.xxx.xxx)

電價表

台電電價來源

  • 兩個月抄表一次,計費時各段度數係加倍計算
  1. 住宅用
每月用電度數分段 夏 月 (6/1~9/30) 非 夏 月
120 度以下部分 1.63 1.63
121~330 度部分 2.38 2.10
331~500 度部分 3.52 2.89
501~700 度部分 4.80 3.94
701~1000 度部分 5.83 4.74
1001 度以上部分 7.69 6.03
-- 非夏月住宅用電
CREATE TABLE `basic information`.`electric_price_living_normal` ( 
`Less_than_120` DOUBLE NOT NULL DEFAULT '1.63',
`Between_121_and_330` DOUBLE NOT NULL DEFAULT '2.10', 
`Between_331_and_500` DOUBLE NOT NULL DEFAULT '2.89', 
`Between_501_and_700` DOUBLE NOT NULL DEFAULT '3.94',
`Between_701_and_1000` DOUBLE NOT NULL DEFAULT '4.74',
`Above_1001` DOUBLE NOT NULL DEFAULT '6.03' ) ENGINE = InnoDB;
-- 夏月住宅用電
CREATE TABLE `basic information`.`electric_price_living_summer` ( 
`Less_than_120` DOUBLE NOT NULL DEFAULT '1.63' ,
`Between_121_and_330` DOUBLE NOT NULL DEFAULT '2.38', 
`Between_331_and_500` DOUBLE NOT NULL DEFAULT '3.52', 
`Between_501_and_700` DOUBLE NOT NULL DEFAULT '4.80',
`Between_701_and_1000` DOUBLE NOT NULL DEFAULT '5.83',
`Above_1001` DOUBLE NOT NULL DEFAULT '7.69' ) ENGINE = InnoDB;
  1. 營業用
每月用電度數分段 夏 月 (6/1~9/30) 非 夏 月
330 度以下部分 2.53 2.12
331~700 度部分 3.55 2.91
701~1500 度部分 4.25 3.44
1501~3000 度部分 6.62 5.20
3001 度以上部分 6.75 5.30
-- 非夏月商業用電
CREATE TABLE `basic information`.`electric_price_commercial_normal` ( 
`Less_than_330` DOUBLE NOT NULL DEFAULT '2.12' ,
`Between_331_and_700` DOUBLE NOT NULL DEFAULT '2.91', 
`Between_701_and_1500` DOUBLE NOT NULL DEFAULT '3.44',
`Between_1501_and_3000` DOUBLE NOT NULL DEFAULT '5.20',
`Above_3001` DOUBLE NOT NULL DEFAULT '5.30' ) ENGINE = InnoDB;
-- 夏月商業用電
CREATE TABLE `basic information`.`electric_price_commercial_summer` ( 
`Less_than_330` DOUBLE NOT NULL DEFAULT '2.53' ,
`Between_331_and_700` DOUBLE NOT NULL DEFAULT '3.55', 
`Between_701_and_1500` DOUBLE NOT NULL DEFAULT '4.25',
`Between_1501_and_3000` DOUBLE NOT NULL DEFAULT '6.62',
`Above_3001` DOUBLE NOT NULL DEFAULT '6.75' ) ENGINE = InnoDB;

台電供需資料

來源:政府資料開放平台,內容為從去年至 3 月的每天電力供需資料。
各發電廠皆有電力產出資料,我暫時先把每個發電廠的供給資料刪除以免過冗
下表為我目前簡易整理出的供需電整理:

淨尖峰供電能力(MW) 尖峰負載(MW) 備轉容量(MW) 備轉容量率(%) 工業用電(百萬度) 民生用電(百萬度)
count 396.000000 396.000000 396.000000 396.000000 396.000000 396.000000
mean 36092.507576 31750.000000 4299.686869 13.722980 367.921407 274.784210
std 4471.465956 4179.503585 1302.309302 4.403209 32.538933 46.397207
min 26200.000000 21459.000000 1316.000000 3.370000 259.930000 192.828000
25% 32911.000000 29126.250000 3300.500000 10.400000 348.981250 240.720750
50% 35125.500000 31024.500000 4155.500000 13.030000 371.485500 265.638500
75% 39994.250000 34500.500000 5110.750000 15.985000 391.870000 305.354000
max 44947.000000 40748.000000 9073.000000 31.060000 429.990000 404.301000

SQL schema table

schema 敘述連結

electricity record

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

Month>月份僅對照下個table做夏季與非夏季區別
Date>詳細的年/月/日

Electricity season

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

E_no>夏季與非夏季區別代號
E_name>夏季與非夏季名稱

Difference

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

Diff_no>商用與家用區別代號
D_name>商用與家用名稱

Business electricity fee / Residential electricity fee

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

Level>用電級距
Rate>費率

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

員工流失資料

來源:Kaggle Employee Attrition for Healthcare

我對於原始資料做了不少整理,以下細述我整理內容:

  • 刪除所有 row 皆相同的 column,刪掉的 column 有 EmployeeCount, Over18, StandardHours
  • AttributionOverTime 屬性原為 YesNo,轉為
    1
    0
    的編碼
  • BusinessTravel 有三種可能的數值:Non-TravelTravel_RarelyTravel_Frequently,轉成 one-hot encoding
  • MaritalStatus 有三種可能的數值:SingleMarriedDivorced,轉成 one-hot encoding