相交N width_buckets

Question

我试着从带桶的列中提取子集，然后取交叉口。

这将从原始表中选择其他列。

我也对串联过滤开放。

下面的代码报告col1不存在--不确定它是否是正确的方法。

WITH ranges AS (
    SELECT 
    min(col1) AS c1min, 
    max(col1) AS c1max,
    min(col2) AS c2min,
    max(col2) AS c2max
    FROM csv_test
),
f1 AS (
    SELECT width_bucket(col1,c1min,c1max,12) AS b1
    FROM csv_test, ranges 
    ORDER BY b1 ASC
),
f2 AS (
    SELECT width_bucket(col2,c2min,c2max,12) AS b2
    FROM csv_test, ranges 
    ORDER BY b2 ASC
)
SELECT b1, b2, c3, c4, c18
FROM csv_test
WHERE 
b1 BETWEEN 0 AND 5
AND
b2 BETWEEN 3 AND 7;

Answer 1

您可以使用LATERAL连接：

SELECT t.*, s2.*
FROM csv_test t
,LATERAL (SELECT 
           min(col1) AS c1min, 
           max(col1) AS c1max,
           min(col2) AS c2min,
           max(col2) AS c2max
           FROM csv_test) AS s
,LATERAL (SELECT width_bucket(col1,c1min,c1max,12) AS b1,
                 width_bucket(col2,c2min,c2max,12) AS b2) AS s2
WHERE b1 BETWEEN 0 AND 5
  AND b2 BETWEEN 3 AND 7;

DBFiddle演示