我在确定如何在列中输入条目时遇到了一些困难
event_name其中match_id = 1
并将该条目应用于由visit_id和pattern_id分区的新列中的每个记录
…
您的结果似乎来自这样的查询:
SELECT event_label as Search, product_list_name, SKU, SUM( (event_name = 'Impression')::int ) AS Impressions, SUM( (event_name = 'Click')::int ) AS Clicks FROM t WHERE SKU IS NOT NULL GROUP BY vent_label, product_list_name, SKU;
您可能想要包含 pattern_id 在里面 GROUP BY ,但它不在结果集中,所以我不确定。
pattern_id
GROUP BY
这可能接近你想要的:
SELECT t2.event_label AS Search, t1.product_list_name, t1.SKU, COUNT(CASE WHEN t1.event_name = 'Impression' THEN 1 END) AS Impression, COUNT(CASE WHEN t1.event_name = 'Click' THEN 1 END) AS Click FROM mytable AS t1 INNER JOIN mytable AS t2 ON t1.Visit_ID = t2.Visit_ID AND t2.match_id = 1 WHERE t1.match_id > 1 GROUP BY t1.Visit_ID, t1.SKU, t2.event_label, t1.product_list_name
查询选择所有记录 match_id <> 1 将它们中的每一个与相应的相匹配 match_id = 1 记录。它用 条件聚合 计算数量 Impressions 和 Clicks 每 Visit_ID 和 SKU 。
match_id <> 1
match_id = 1
Impressions
Clicks
Visit_ID
SKU