Looker新手求助：获取特定产品购买者上一台设备并保存Look

一、获取目标数据的SQL逻辑 #

要筛选购买特定产品的客户并获取其上一台设备，可通过窗口函数结合子查询实现，以下是基于你现有字段（customer id、smartphone names、date）的示例代码：

WITH customer_purchases AS (
  SELECT
    `customer id` AS customer_id,
    `smartphone names` AS device_name,
    `date` AS purchase_date,
    -- 按客户分组，按购买日期倒序排序，标记购买顺序
    ROW_NUMBER() OVER (PARTITION BY `customer id` ORDER BY `date` DESC) AS purchase_rank
  FROM your_table_name -- 替换为你的实际表名
),
target_customers AS (
  -- 提取购买目标产品（示例为'Phone 1'）的客户及对应购买日期
  SELECT
    customer_id,
    purchase_date AS target_purchase_date
  FROM customer_purchases
  WHERE device_name = 'Phone 1' -- 替换为你要筛选的产品
)
SELECT
  tc.customer_id,
  cp.device_name AS previous_device
FROM target_customers tc
JOIN customer_purchases cp ON tc.customer_id = cp.customer_id
-- 匹配目标客户购买目标产品前的最近一次购买记录
WHERE cp.purchase_date < tc.target_purchase_date
AND cp.purchase_rank = (
  SELECT MIN(purchase_rank)
  FROM customer_purchases cp_inner
  WHERE cp_inner.customer_id = tc.customer_id
  AND cp_inner.purchase_date < tc.target_purchase_date
)

逻辑说明 #

• customer_purchases：给每个客户的购买记录按日期倒序排秩，最新购买记录排第1
• target_customers：筛选出购买目标产品的客户ID和对应购买日期
• 最后关联两个CTE，定位目标客户在购买目标产品前的最近一次购买记录，即为所需的上一台设备

也可以用LAG()函数简化逻辑，适合无需额外复杂筛选的场景：

WITH customer_purchases AS (
  SELECT
    `customer id` AS customer_id,
    `smartphone names` AS device_name,
    `date` AS purchase_date,
    -- 获取同一客户的上一次购买设备
    LAG(`smartphone names`) OVER (PARTITION BY `customer id` ORDER BY `date`) AS previous_device
  FROM your_table_name
)
SELECT
  customer_id,
  previous_device
FROM customer_purchases
WHERE device_name = 'Phone 1' -- 筛选购买目标产品的记录
AND previous_device IS NOT NULL -- 排除首次购买该产品的客户

二、解决无法保存Look的问题 #

SQL Runner中编写代码后无法保存Look，可从以下几个方向排查解决：

• 检查SQL有效性：先在SQL Runner中执行代码，确认能正常返回结果，修复语法错误（如字段名拼写、表名错误、窗口函数语法问题）
• 核实权限：确认你的Looker账号拥有创建/保存Look的权限，若无则联系管理员分配
• 规范保存配置：保存时确保Look名称合规，选择你有权访问的文件夹路径
• 适配可视化类型：保存前需选择合适的可视化（如表格），确保数据结构适配所选类型
• 修复页面异常：刷新SQL Runner页面，清除浏览器缓存后重试保存操作

内容的提问来源于stack exchange，提问作者anshika goel