JOIN中的BigQuery子查询不识别字段 [英] BigQuery subselect in JOIN does not recognize fields

问题描述

每个用户都有一个包含多个过期快照的表格和一个包含每个用户的最新快照日期的表格（通过查询生成）。我已经尝试了一些变体来让两个人的简单连接起作用，但我没有运气。我想从快照表中选择与其他表中的用户ID和日期相匹配的所有记录。

我有很多错误，但这是最新的（子选择和重命名是为了调试可能导致问题的字段）：

  SELECT t1.uuid，t1.username，t1.d 
 FROM（SELECT uuid，username，date AS d FROM [Activity .user_snapshots]）as t1 
 JOIN EACH（SELECT uuid，date AS dg FROM [Activity.latest_snapshots]）as t2 
 ON t1.uuid = t2.uuid AND t1.d = t2.dg; 
  

我在这种情况下得到的错误回应是：

 错误：在表'__S0'中找不到字段'dg'。 
  

当我尝试了更直接的查询时：

  SELECT t1.uuid，t1.username，t1.date 
 FROM [Activity.user_snapshots] as t1 
 JOIN EACH [Activity.latest_snapshots ] as t2 
 ON t1.uuid = t2.uuid AND t1.date = t2.date; 
  

我得到这个错误：

 错误：来自表__S0的字段日期不是叶字段。 
  

有什么想法？

解决方案

有一个连接时间戳值的错误。如果你强迫它们达到它们的基本微秒值，你应该很好。此查询应该可以正常工作：

  SELECT t1.uuid，t1.username，USEC_TO_TIMESTAMP（t1.d）
 FROM 
 SELECT uuid，username，TIMESTAMP_TO_USEC（date）AS d 
 FROM [Activity.user_snapshots]）as t1 
 JOIN EACH（
 SELECT uuid，TIMESTAMP_TO_USEC（date）AS dg 
 FROM [Activity.latest_snapshots]）as t2 
 ON t1.uuid = t2.uuid AND t1.d = t2.dg; 
  

I've got a table with multiple dated snapshots per user and a table with the latest date of the snapshot for each user (generated via a query).

I've tried a number of variations to get a simple join of the two to work but I'm having no luck. I want to select all records from the snapshots table that match the user id and date from the other table.

I've gota variety of errors, but this is the latest (sub-selects and renames were done to debug what field might be causing the problem):

SELECT t1.uuid, t1.username, t1.d 
  FROM (SELECT uuid, username, date AS d FROM [Activity.user_snapshots]) as t1
  JOIN EACH (SELECT uuid, date AS dg FROM [Activity.latest_snapshots]) as t2
  ON t1.uuid = t2.uuid AND t1.d = t2.dg;

The error response that I get in this case is:

Error: Field 'dg' not found in table '__S0'.

When I've tried the much more straight-forward query:

SELECT t1.uuid, t1.username, t1.date
  FROM [Activity.user_snapshots] as t1
  JOIN EACH [Activity.latest_snapshots] as t2
  ON t1.uuid = t2.uuid AND t1.date = t2.date;

I get this error:

Error: Field date from table __S0 is not a leaf field.

Any ideas?

解决方案

There is a bug joining on timestamp values. If you coerce them to their underlying microsecond values, you should be good. This query should work:

SELECT t1.uuid, t1.username, USEC_TO_TIMESTAMP(t1.d)
  FROM (
    SELECT uuid, username, TIMESTAMP_TO_USEC(date) AS d 
    FROM [Activity.user_snapshots]) as t1
  JOIN EACH (
    SELECT uuid, TIMESTAMP_TO_USEC(date) AS dg 
    FROM [Activity.latest_snapshots]) as t2
  ON t1.uuid = t2.uuid AND t1.d = t2.dg;

这篇关于JOIN中的BigQuery子查询不识别字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！