如何在复杂查询中使用_table_suffix限制数据集？ [英] How to limit datasets using _table_suffix on complex query?

问题描述

我了解 _TABLE_SUFFIX 如何在更简单的查询中成功使用它。我目前正在尝试构建一个应用程序，该应用程序将从100个以上的数据集中获取活动用户，但已经陷入资源限制。为了绕过这些资源限制，我将多次循环并运行查询，并使用 _TABLE_SUFFIX 来限制它一次选择多少。

以下是我当前的查询：

pre $ WITH allTables AS（SELECT
app，
日期，
CON（以CASE当周期= 30 THEN users END）as days_30
FROM（
SELECT
CONCAT（user_dim.app_info.app_id，'：'，user_dim .app_info.app_platform）as app，
dates.date as date，
periods.period as period，
COUNT（DISTINCT user_dim.app_info.app_instance_id）as user
FROM`table .app_events_ *`作为活动
WHERE _TABLE_SUFFIX'20170101'和'20170502'之间
或_TABLE_SUFFIX'intraday_20170101'和'intraday_20170502'之间
CROSS JOIN
UNNEST（event_dim）AS事件
CROSS JOIN（
SELECT DISTINCT
TIMESTAMP_TRUNC（TIMESTAMP_MICROS（event.timestamp_micros），DAY，'UTC'）作为日期
FROM`table.app_events_ *`
WHERE _TABLE_SUFFIX'20170101'与'20170502'之间'
'或'_TABLE_SUFFIX''intraday_20170101'与'intraday_20170502'

CROSS JOIN
UNNEST （event_dim）作为事件）作为日期
CROSS JOIN（
SELECT
期间
FROM（
SELECT 30作为期间
）
）as期间
WHERE
dates.date> = TIMESTAMP_TRUNC（TIMESTAMP_MICROS（event.timestamp_micros），DAY，'UTC'）
AND
FLOOR（TIMESTAMP_DIFF（dates.date，TIMESTAMP_MICROS（ event.timestamp_micros），DAY）/periods.period）= 0
GROUP BY 1,2,3
）
GROUP BY 1,2）
SELECT
app作为目标，
UNIX_SECONDS（date）as datapoint_time，
SUM（days_30）as datapoint_value
FROM allTables
WHERE date> = TIMESTAMP_ADD（TIMESTAMP_TRUNC（CURRENT_TIMESTAMP，Day，'UTC' ），INTERVAL -30 DAY）
GROUP BY日期，1
ORDER BY日期ASC


目前这给了我：

错误：语法错误：Expected ）但是在[14：3]获得了关键字CROSS

所以我的问题是，如何限制数据量I拉入使用此查询和 _TABLE_SUFFIX ？我觉得我在这里错过了很简单的事情。任何帮助都会很棒，谢谢！

CROSS JOIN UNNEST（event_dim）AS event code>（以及它后面的交叉连接）需要在WHERE子句之前。您可以在查询语法文档中阅读更多内容。

I understand how _TABLE_SUFFIX works and have successfully used it before on simpler queries. I'm currently trying to build an application that will get active users from 100+ datasets but have been running into resource limits. In order to bypass these resource limits I'm going to loop and run the query multiple times and limit how much it selects at once using _TABLE_SUFFIX.

Here is my current query:

WITH allTables AS (SELECT
  app,
  date,
  SUM(CASE WHEN period = 30  THEN users END) as days_30
FROM (
  SELECT
    CONCAT(user_dim.app_info.app_id, ':', user_dim.app_info.app_platform) as app,
    dates.date as date,
    periods.period as period,
    COUNT(DISTINCT user_dim.app_info.app_instance_id) as users
  FROM `table.app_events_*` as activity
    WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170502'
    OR _TABLE_SUFFIX BETWEEN 'intraday_20170101' AND 'intraday_20170502'
  CROSS JOIN
    UNNEST(event_dim) AS event
  CROSS JOIN (
    SELECT DISTINCT
      TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event.timestamp_micros), DAY, 'UTC') as date
    FROM `table.app_events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20170101' AND '20170502'
    OR _TABLE_SUFFIX BETWEEN 'intraday_20170101' AND 'intraday_20170502'

    CROSS JOIN
        UNNEST(event_dim) as event) as dates
    CROSS JOIN (
      SELECT
        period
      FROM (
        SELECT 30 as period
      )
    ) as periods
    WHERE
      dates.date >= TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event.timestamp_micros), DAY, 'UTC')
    AND
      FLOOR(TIMESTAMP_DIFF(dates.date, TIMESTAMP_MICROS(event.timestamp_micros), DAY)/periods.period) = 0
    GROUP BY 1,2,3
  )
  GROUP BY 1,2) 
SELECT
 app as target,
 UNIX_SECONDS(date) as datapoint_time,
 SUM(days_30) as datapoint_value
FROM allTables
WHERE date >= TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, Day, 'UTC'), INTERVAL -30 DAY)
GROUP BY date,1
ORDER BY date ASC

This currently gives me:

Error: Syntax error: Expected ")" but got keyword CROSS at [14:3]

So my question is, how can I limit the amount of data I pull in using this query and _TABLE_SUFFIX? I feel like I'm missing something very simple here. Any help would be great, thanks!

解决方案

The CROSS JOIN UNNEST(event_dim) AS event (and the cross join following it) needs to come before the WHERE clause. You can read more in the query syntax documentation.

这篇关于如何在复杂查询中使用_table_suffix限制数据集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！