如何在SQL中按日期统计不同订单金额的对应数量？

我需要执行一项统计操作，按订单金额值统计每日的订单数量。订单金额仅包含1000或500两种取值，需按日期返回对应金额的统计数量。

原始数据表 #

"ORD_NUM","ORD_AMOUNT","ORD_DATE","CUST_CODE","AGENT_CODE","ORD_DESCRIPTION"
"200118"|"500"|"07/20/2008"|"C00023"|"A006"|"SOD"
"200120"|"500"|"07/20/2008"|"C00009"|"A002"|"SOD"
"200129"|"1000"|"07/20/2008"|"C00024"|"A006"|"SOD"
"200127"|"1000"|"08/20/2008"|"C00015"|"A003"|"SOD"
"200128"|"500"|"08/20/2008"|"C00009"|"A002"|"SOD"
"200128"|"500"|"09/20/2008"|"C00009"|"A002"|"SOD"
"200128"|"1000"|"09/20/2008"|"C00009"|"A002"|"SOD"
"200128"|"1000"|"10/20/2008"|"C00009"|"A002"|"SOD"

期望输出示例 #

Date      |1000Count|500Count
07/20/2008|    1    |   2
08/20/2008|    1    |   1
09/20/2008|    1    |   1
10/20/2008|    1    |   1

实现方案 #

1. SQL 实现 #

使用条件聚合函数，按日期分组后统计对应金额的订单数：

SELECT
    ORD_DATE AS Date,
    SUM(CASE WHEN ORD_AMOUNT = '1000' THEN 1 ELSE 0 END) AS 1000Count,
    SUM(CASE WHEN ORD_AMOUNT = '500' THEN 1 ELSE 0 END) AS 500Count
FROM
    your_table_name  -- 替换为你的实际表名
GROUP BY
    ORD_DATE
ORDER BY
    ORD_DATE;

2. Python Pandas 实现 #

通过分组和透视表实现统计：

import pandas as pd

# 读取数据，注意分隔符为|
df = pd.read_csv('your_data_file.csv', sep='|')

# 按日期分组，统计各金额的订单数并重塑结构
counts = df.groupby(['ORD_DATE', 'ORD_AMOUNT']).size().unstack(fill_value=0)
# 调整列名和顺序
counts = counts.rename(columns={'500': '500Count', '1000': '1000Count'})
counts = counts.reset_index().rename(columns={'ORD_DATE': 'Date'})
counts = counts[['Date', '1000Count', '500Count']]

# 打印结果
print(counts.to_string(index=False))

内容的提问来源于stack exchange，提问作者Chanikya