sql-having优化

最新推荐文章于 2024-10-05 00:24:42 发布

~kiss~

最新推荐文章于 2024-10-05 00:24:42 发布

阅读量240

点赞数 2

分类专栏： sql 文章标签： sql 数据库

本文链接：https://blog.csdn.net/wangkai6666/article/details/140741840

版权

sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

最开始写的

with animals as (
    select  cast(id as bigint) as id
    from    tbl1
    where   date='${date}'
    and     scene=2
    and     type=0
    and     create_time>=unix_timestamp(
                date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 30),
                'yyyy-MM-dd'
            )
)
select  animal_id, count(1) as num
from    (
            select  distinct
                    animal_id,
                    create_time
            from    tbl2
            where   date='${date}'
            and     id in (
                        select  id
                        from    animals
                    )
            group by
                    animal_id,
                    create_time
        )
group by
        animal_id
having  count(1)>1

优化后的

with animals as (
    select  cast(id as bigint) as id
    from    tbl1
    where   date='${date}'
    and     scene=2
    and     type=0
    and     create_time>=unix_timestamp(
                date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), 30),
                'yyyy-MM-dd'
            )
)
 
select  animal_id,
        COUNT(distinct create_time) as num
from    tbl2
where   date='${date}'
and     id in (
            select  id
            from    animals
        )
group by
        animal_id
having  COUNT(distinct create_time)>1