Leetcode 解鎖 - Monthly Transactions I

請在表1查找每個月份和國家的交易數量及其總金額、已批准交易的數量及其總金額(如表2)，結果可以以任何順序返回。

請使用下列三種語法查找:

1. MS SQL Server 查詢

2. MySQL 查詢

3. Pandas 查詢

表1

表2

SQL

兩大重點 -

1. 如何將trans_date，轉成YYYY-MM格式:

FORMAT() 函數是 SQL Server 中用來格式化數據的工具。它允許你指定日期、時間或數字的顯示格式。以下是一些常用的日期和時間格式化字符串：

在 MySQL 中，可以使用 DATE_FORMAT() 函數來格式化日期和時間。這個函數的功能類似於 SQL Server 的 FORMAT() 函數。以下是常見的格式化符號:

2. 如何在條件式計算count和sum:

在 SQL 中，SUM(CASE WHEN ... THEN ... ELSE ... END) 可以用來實現條件式計算的常見方法。這種結構可以用來計算符合特定條件的數據的總和或計數。我們也可以用IIF(MYSQL: IF)來取代CASE WHEN，來達到條件計算。

以下是MS SQL Sever和MYSQL的程式碼:

//MS SQL Sever
//CASE WHEN
SELECT 
    FORMAT(trans_date, 'yyyy-MM') AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    FORMAT(trans_date, 'yyyy-MM'),
    country;

//IIF
SELECT 
    FORMAT(trans_date, 'yyyy-MM') AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(IIF(state = 'approved', 1, 0)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    FORMAT(trans_date, 'yyyy-MM'),
    country;    

//MYSQL Sever
//CASE WHEN
SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    month, country;
 
//IF
SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(IF(state = 'approved', 1, 0)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    month, country;
    

Python

1. 將 trans_date 轉換為 datetime 格式，並使用.to_period("M")產生新的欄位月份。
2. 分組groupby計算: 使用 groupby(['month', 'country']) 按月份和國家進行分組，然後使用 agg() 函數來計算每組的交易數量、已批准交易的數量、交易總金額和已批准交易的總金額
3. 使用lambda處理條件(state = 'approved')

以下是Python 的程式碼:

import pandas as pd

transactions['trans_date'] = pd.to_datetime(transactions['trans_date'])
transactions['month'] = transactions['trans_date'].dt.to_period('M')


result = transactions.groupby(['month', 'country']).agg(
trans_count=('id', 'count'),
approved_count=('id', lambda x: x[transactions.loc[x.index, 'state'] == 'approved'].count()),
trans_total_amount=('amount', 'sum'),
approved_total_amount=('amount', lambda x: x[transactions.loc[x.index, 'state'] == 'approved'].sum())
).reset_index()


result['month'] = result['month'].astype(str)

謝謝您花時間將此篇文章讀完，若覺得對您有幫助可以幫忙按個讚、分享來或是珍藏喔!也歡迎Follow我的Threads，持續追蹤生產力工具、商業分析、商業英文，提升自己的職場力喔!