英文:
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 = "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
(
-- 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 'Asia/Karachi';
-- Replace list of user_pseudo_id's with ones you want to query.
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
-- 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 "(" 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论