根据公式，编写SQL查询语句。
Description
The upload/download traffic is accumulated by each session until end (the key of each session is
CUST_SRV_GRP_ID+ MAC_ADDR + Session ID
Each session cycle must have status START / ALIVE / STOP
Status = START (new session), ALIVE (cutover) , STOP (session end), a session may cross several days, assume a session with at least one ALIVE each day if not STOP (blank on status = alive)
Task1: Create view daily_traffic_summary
Create a view base on DM_PCD_USAGE_DAILY which keep the latest upload / download traffic & counters by the key of CUST_SRV_GRP_ID + MAC_ADDR + Session ID
Keep the latest status on each session each batch date (e.g. START =1 , ALIVE = 2, STOP = 3) , sort by CUST_SRV_GRP_ID + MAC_ADDR + Session ID + status sequence in descending and take the higher status by the key of CUST_SRV_GRP_ID + MAC_ADDR + Session ID
Keep the BATCH_DATE and BSN in daily_traffic_summary
Keep record of recent 1 months
Description of existing table DM_PCD_USAGE_MONTH_SUMMARY
Table structure of DM_PCD_USAGE_MONTH_SUMMARY:
Example data of DM_PCD_USAGE_MONTH_SUMMARY:
Task 2: Update table DM_PCD_USAGE_MONTH_SUMMARY
Calculate TOTAL_UPLOAD_TRAFFIC_GB and TOTAL_DOWNLOAD_TRAFFIC_GB from existing table
DM_PCD_USAGE_DAILY for current month (the session cross months should minus the last month traffic for this month summary)
Summary the UP&DOWNLOAD_TRAFFIC_BYTE and UP&DOWNLOAD TRAFFIC COUNTER in this month for each CUST_SRV_GRP_ID
Formula provided as below:
TOTAL_UPLOAD_TRAFFIC_GB = (Summary UPLOAD TRAFFIC COUNTER) * 4 + (Summary UPLOAD_TRAFFIC_BYTE / 1024/1024/1024)
Update the result in the table