BigQuery会话编号不像样板示例上那样工作。

huangapple go评论46阅读模式
英文:

Bigquery session number not working as on boilerplate example

问题

以下是您提供的代码的翻译部分:

我正在关注这个示例:
https://developers.google.com/analytics/bigquery/advanced-queries#user_pseudo_id

我对上述示例进行了三个主要更改,第一个是更改时区,第二个是传递我自己的user_pseudo_id列表(来自“removed cte),第三个是将持续时间更改为30天而不是4天(使用BETWEEN GetDateSuffix(-30))。当然,我还更改了我的数据表!

这是我修改后的代码版本:

with removed as (

SELECT distinct user_pseudo_id as removed_users
from `rayn-deen-app.analytics_317927526.events_*`
where event_name = "app_remove"

and

DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY) 
between DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
and
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

)
,
session_counts as

(
-- 获取特定用户在过去4天内的最新ga_session_id和ga_session_number。

-- 替换时区。列表位于https://en.wikipedia.org/wiki/List_of_tz_database_time_zones。

DECLARE REPORTING_TIMEZONE STRING DEFAULT 'Asia/Karachi';

-- 用要查询的用户的user_pseudo_id列表替换。
DECLARE USER_PSEUDO_ID_LIST ARRAY<STRING> DEFAULT
  select * from removed;

CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
  (SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);

CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
  (SELECT FORMAT_DATE('%Y%m%d', DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);

SELECT DISTINCT
  user_pseudo_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_id').int_value)
    OVER (UserWindow) AS ga_session_id,
  FIRST_VALUE(GetParamValue(event_params, 'ga_session_number').int_value)
    OVER (UserWindow) AS ga_session_number
FROM
  -- 替换表名。
  `rayn-deen-app.analytics_317927526.events_*`
WHERE
  user_pseudo_id IN UNNEST(USER_PSEUDO_ID_LIST)
  AND RIGHT(_TABLE_SUFFIX, 8)
    BETWEEN GetDateSuffix(-30, REPORTING_TIMEZONE)
    AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC))

select * from session_counts

我遇到了以下错误:

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword DECLARE at [23:1]

如果您需要进一步的帮助,请告诉我。

英文:

I am following this example:
https://developers.google.com/analytics/bigquery/advanced-queries#user_pseudo_id

I have made three main changes to the above, the first is to change the timezone,
the second is to pass my own list of user_pseudo_id (which comes from the "removed" cte), the third is to change the duration to last 30 days instead of 4 (by using BETWEEN GetDateSuffix(-30). And ofcourse I changed my data table!

Here is my modified version of the code:

with removed as (
SELECT distinct user_pseudo_id as removed_users
from `rayn-deen-app.analytics_317927526.events_*`
where event_name = &quot;app_remove&quot;
and
DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY) 
between DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
and
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
,
session_counts as
(
-- Get the latest ga_session_id and ga_session_number for specific users during last 4 days.
-- Replace timezone. List at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
DECLARE REPORTING_TIMEZONE STRING DEFAULT &#39;Asia/Karachi&#39;;
-- Replace list of user_pseudo_id&#39;s with ones you want to query.
DECLARE USER_PSEUDO_ID_LIST ARRAY&lt;STRING&gt; DEFAULT
select * from removed;
CREATE TEMP FUNCTION GetParamValue(params ANY TYPE, target_key STRING)
AS (
(SELECT `value` FROM UNNEST(params) WHERE key = target_key LIMIT 1)
);
CREATE TEMP FUNCTION GetDateSuffix(date_shift INT64, timezone STRING)
AS (
(SELECT FORMAT_DATE(&#39;%Y%m%d&#39;, DATE_ADD(CURRENT_DATE(timezone), INTERVAL date_shift DAY)))
);
SELECT DISTINCT
user_pseudo_id,
FIRST_VALUE(GetParamValue(event_params, &#39;ga_session_id&#39;).int_value)
OVER (UserWindow) AS ga_session_id,
FIRST_VALUE(GetParamValue(event_params, &#39;ga_session_number&#39;).int_value)
OVER (UserWindow) AS ga_session_number
FROM
-- Replace table name.
`rayn-deen-app.analytics_317927526.events_*`
WHERE
user_pseudo_id IN UNNEST(USER_PSEUDO_ID_LIST)
AND RIGHT(_TABLE_SUFFIX, 8)
BETWEEN GetDateSuffix(-30, REPORTING_TIMEZONE)
AND GetDateSuffix(0, REPORTING_TIMEZONE)
WINDOW UserWindow AS (PARTITION BY user_pseudo_id ORDER BY event_timestamp DESC))
select * from session_counts

I am facing the following error:

Syntax error: Expected &quot;(&quot; or keyword SELECT or keyword WITH but got keyword DECLARE at [23:1]

答案1

得分: 1

我看了一下你的代码和错误。看起来你不能在CTE中声明和创建变量。建议在CTE之外声明这些变量。我认为这些定义在这里更好地解释了。

英文:

I took a little peek with your code and the error. It appears that you cannot use declare and create variables within the CTE. It is advised to declare these variables outside a CTE. I believe such definitions are better explained here.

huangapple
  • 本文由 发表于 2023年5月22日 22:43:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76307349.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定