时间戳

首页 > 解决方案 > 如何在 redash 中插入基于计数结果的查询过滤器

sql - 如何在 redash 中插入基于计数结果的查询过滤器

问题描述

我相信这是一个简单的问题，但由于我不熟悉 redash，所以它似乎很难。

我有一个redash中的查询，

SELECT si.variant_id,
       v.sku,
       COUNT(CASE
                 WHEN a.viewable_type = 'Spree::Variant' THEN a.id
             END) AS photo
FROM spree_stock_items si
LEFT JOIN spree_stock_locations sl ON sl.id = si.stock_location_id
LEFT JOIN spree_assets a ON si.variant_id = a.viewable_id
LEFT JOIN spree_variants v ON v.id = si.variant_id
WHERE-- viewable_type = 'Spree::Variant'
sl.name='{{store_location}}'
  AND si.deleted_at IS NULL
  AND sl.country_id = '2'
GROUP BY 1,
         2
ORDER BY 1
LIMIT 200000

给出这样的结果： 在此处输入图像描述

截至目前，我可以在store location. 但现在我想query filter根据列添加photo。我该怎么做？store location基本上我想通过列中的和计数来过滤redash仪表板中的结果photo。

标签： sqlpostgresqlcounthaving-clauseredash

解决方案

您可以使用having子句 - 但它需要重复聚合表达式。例如：

SELECT si.variant_id,
       v.sku,
       COUNT(a.id) FILTER(WHERE a.viewable_type = 'Spree::Variant') AS photo
FROM spree_stock_items si
LEFT JOIN spree_stock_locations sl ON sl.id = si.stock_location_id
LEFT JOIN spree_assets a ON si.variant_id = a.viewable_id
LEFT JOIN spree_variants v ON v.id = si.variant_id
WHERE-- viewable_type = 'Spree::Variant'
    sl.name='{{store_location}}'
    AND si.deleted_at IS NULL
    AND sl.country_id = '2'
GROUP BY 1, 2
HAVING COUNT(a.id) FILTER(WHERE a.viewable_type = 'Spree::Variant') > 1
ORDER BY 1
LIMIT 200000

请注意，我重写了count()使用标准filter子句，这使得语法更整洁。

WHERE或者，您可以在外部查询中使用子查询和子句：

SELECT *
FROM (
    SELECT si.variant_id,
           v.sku,
           COUNT(a.id) FILTER(WHERE a.viewable_type = 'Spree::Variant') AS photo
    FROM spree_stock_items si
    LEFT JOIN spree_stock_locations sl ON sl.id = si.stock_location_id
    LEFT JOIN spree_assets a ON si.variant_id = a.viewable_id
    LEFT JOIN spree_variants v ON v.id = si.variant_id
    WHERE-- viewable_type = 'Spree::Variant'
    sl.name='{{store_location}}'
      AND si.deleted_at IS NULL
      AND sl.country_id = '2'
    GROUP BY 1, 2
) t
WHERE photo > 1
ORDER BY 1
LIMIT 200000

推荐阅读