mysql - 如何将以下 3 个 select 语句包含在单个 mySQL 视图中

Question

需要创建执行以下 3 个选择语句的单个视图

我可以在 SQL 编辑器中执行它们并获得比率。

select @lag := count(twitter_handles) from handle_impressions where today_impressions > yest_impressions;
select @decay := count(twitter_handles) from handle_impressions where today_impressions < yest_impressions;
select @ratio := @lag/@decay;

目的是拥有一个封装 3 个选择语句以返回滞后、衰减和比率的单一视图

标签： mysqlview

Answer 1

我们可以尝试使用条件聚合编写单个查询：

SELECT
    COUNT(CASE WHEN today_impressions > yest_impressions THEN 1 END) AS lag_val,
    COUNT(CASE WHEN today_impressions < yest_impressions THEN 1 END) AS decay_val,
    COUNT(CASE WHEN today_impressions > yest_impressions THEN 1 END) /
        COUNT(CASE WHEN today_impressions < yest_impressions THEN 1 END) AS ratio
FROM handle_impressions;