DataStore 聚合函数

from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")

from chdb import datastore as pd

ds = pd.read_csv("sales.csv")

# 单列聚合
total = ds['amount'].sum()
average = ds['amount'].mean()
count = ds['amount'].count()

# 所有聚合操作
print(ds['amount'].sum())    # 总和
print(ds['amount'].mean())   # 平均值
print(ds['amount'].std())    # 标准差
print(ds['amount'].median()) # 中位数
print(ds['amount'].nunique()) # 唯一值数量

# 分组并聚合
result = ds.groupby('category')['amount'].sum()
result = ds.groupby('region')['sales'].mean()

# 字典语法
result = ds.groupby('category').agg({
    'amount': 'sum',
    'quantity': 'mean',
    'order_id': 'count'
})

# 每列的聚合列表
result = ds.groupby('category').agg({
    'amount': ['sum', 'mean', 'max'],
    'quantity': ['sum', 'count']
})

# 命名聚合（pandas 风格）
result = ds.groupby('region').agg(
    total_amount=('amount', 'sum'),
    avg_quantity=('quantity', 'mean'),
    order_count=('order_id', 'count'),
    max_price=('price', 'max')
)

# 按多列分组
result = ds.groupby(['region', 'category']).agg({
    'amount': 'sum',
    'quantity': 'sum'
})

# 分位数
q50 = ds['amount'].quantile(0.5)  # 中位数
q95 = ds['amount'].quantile(0.95) # 第95百分位数

# 多个分位数
quantiles = ds['amount'].quantile([0.25, 0.5, 0.75])

# 列之间的相关性
correlation = ds[['sales', 'marketing_spend']].corr()

from chdb.datastore import F, Field

# 仅对高价值订单求和
high_value_sum = F.sum_if(Field('amount'), Field('amount') > 1000)

# 统计活跃用户数
active_count = F.count_if(Field('status') == 'active')

# 在 groupby 上下文中
result = ds.groupby('region').agg({
    'total': ('amount', 'sum'),
    'high_value': ('amount', F.sum_if(Field('amount') > 1000)),
})

from chdb.datastore import F, Field

# 按类别收集所有标签
result = ds.groupby('category').agg({
    'all_tags': ('tag', F.group_array()),
    'unique_tags': ('tag', F.group_uniq_array())
})

# 获取各区域排名前 5 的产品
result = ds.groupby('region').agg({
    'top_products': ('product_id', F.top_k(5))
})

from chdb.datastore import F, Field

# 添加行号
ds['row_num'] = F.row_number().over(order_by='date')

# 在分组内排名
ds['rank'] = F.rank().over(
    partition_by='category',
    order_by='sales'
)

# 密集排名（无空缺）
ds['dense_rank'] = F.dense_rank().over(
    partition_by='region',
    order_by=('revenue', 'desc')
)

# 划分为四分位数
ds['quartile'] = F.ntile(4).over(order_by='score')

# 前一个值和后一个值
ds['prev_price'] = F.lag('price', 1).over(order_by='date')
ds['next_price'] = F.lead('price', 1).over(order_by='date')

# 分区中的第一个值和最后一个值
ds['first_order'] = F.first_value('amount').over(
    partition_by='customer_id',
    order_by='date'
)

# 累计计算
ds['running_total'] = ds['amount'].cumsum()
ds['running_max'] = ds['amount'].cummax()

# 按分组计算
ds['group_cumsum'] = ds.groupby('category')['amount'].cumsum()

# 环比
ds['daily_diff'] = ds['sales'].diff(1)
ds['pct_change'] = ds['sales'].pct_change(1)

# 滚动窗口聚合
ds['rolling_avg'] = ds['price'].rolling(window=7).mean()
ds['rolling_sum'] = ds['amount'].rolling(window=30).sum()
ds['rolling_std'] = ds['value'].rolling(window=10).std()

# 扩展窗口
ds['expanding_max'] = ds['price'].expanding().max()
ds['expanding_sum'] = ds['amount'].expanding().sum()

from chdb.datastore import F, Field

# 聚合
F.sum(Field('amount'))
F.avg(Field('price'))
F.count(Field('id'))

# 统计
F.quantile(Field('value'), 0.95)
F.stddev_pop(Field('score'))
F.corr(Field('x'), Field('y'))

# 条件
F.sum_if(Field('amount'), Field('status') == 'completed')
F.count_if(Field('is_active'))

# 字符串
F.length(Field('name'))
F.upper(Field('text'))

# 日期/时间
F.to_year(Field('date'))
F.date_diff('day', Field('start'), Field('end'))

# 数组
F.array_sum(Field('values'))
F.array_avg(Field('scores'))

# 数学
F.abs(Field('delta'))
F.round(Field('price'), 2)
F.floor(Field('value'))
F.ceil(Field('value'))

# 定义窗口帧
window = F.window(
    partition_by='category',
    order_by='date',
    rows_between=(-7, 0)  # 当前行及前7行
)

ds['rolling_avg'] = F.avg(Field('price')).over(window)

# 按销售额列出每个类别排名前 3 的产品
result = (ds
    .assign(rank=F.row_number().over(
        partition_by='category',
        order_by=('sales', 'desc')
    ))
    .filter(ds['rank'] <= 3)
)

# 销售额累计总和
ds['running_total'] = F.sum('amount').over(
    order_by='date',
    rows_between=(None, 0)  # 截至当前行的所有行
)

# 7天移动平均
ds['ma_7'] = F.avg('price').over(
    order_by='date',
    rows_between=(-6, 0)
)

# 同比比较
ds['prev_year_sales'] = F.lag('sales', 12).over(
    partition_by='product_id',
    order_by='month'
)
ds['yoy_growth'] = (ds['sales'] - ds['prev_year_sales']) / ds['prev_year_sales']

# 按客户总消费额排名
ds['spend_percentile'] = F.percent_rank().over(order_by='total_spend')