开发者社区 > 云存储 > 正文

在表格存储中想通过月份分组得到相应统计聚合数据,请问大家有效率高点的替代方案吗?

在表格存储中想通过月份分组得到相应统计聚合数据,请问大家有效率高点的替代方案吗?

select
    case 
        when news_star_time>=1690819200 and news_star_time<1693497600 then '2023-08' 
        when news_star_time>=1693497600 and news_star_time<1696089600 then '2023-09' 
        when news_star_time>=1696089600 and news_star_time<1698768000 then '2023-10' 
        when news_star_time>=1698768000 and news_star_time<1701360000 then '2023-11' 
        when news_star_time>=1701360000 and news_star_time<1704038400 then '2023-12' 
        when news_star_time>=1704038400 and news_star_time<1706716800 then '2024-01' 
        when news_star_time>=1706716800 and news_star_time<1709222400 then '2024-02' 
        when news_star_time>=1709222400 and news_star_time<1711900800 then '2024-03' 
        when news_star_time>=1711900800 and news_star_time<1714492800 then '2024-04' 
        when news_star_time>=1714492800 and news_star_time<1717171200 then '2024-05' 
        when news_star_time>=1717171200 and news_star_time<1719763200 then '2024-06' 
        when news_star_time>=1719763200 and news_star_time<1722441600 then '2024-07' 
        when news_star_time>=1722441600 and news_star_time<1725120000 then '2024-08' 
        when news_star_time>=1725120000 and news_star_time<1727712000 then '2024-09' 
    end as time,
    count(*) as cishu,
    sum(news_zbje) as jine 
from 
    table_name
where 
    news_type=1
    and news_star_time between 1690819200 and 1725120000
    and news_xingzi <> '-'
    and new_Industry_father <> '99'
group by 
    time
order by 
    time ASC
limit 30

展开
收起
花开富贵111 2024-08-12 16:52:53 39 0
0 条回答
写回答
取消 提交回答

阿里云存储基于飞天盘古2.0分布式存储系统,产品多种多样,充分满足用户数据存储和迁移上云需求。

相关电子书

更多
TableStore在社交类场景下的应用 立即下载
表格存储实时数据流Steam的技术揭秘和应用场景 立即下载
表格存储(TableStore) 立即下载