我正在使用BigQuery中的Google Analytics数据,我正在尝试计算 IF(COUNT(DISTINCT hits.transaction.transactionId) > 0,(MAX(DATE))
和 CURRENT_DATE()
之间的DATE_DIFF,但我收到错误消息:
错误:参数类型的函数IF没有匹配的签名:BOOL,STRUCT . 支持的签名:IF(BOOL,ANY,ANY)在[12:11]
这是什么意思? IF
的两个部分必须是同一类型吗?我似乎也有类似的错误,表明MAX(DATE)是一个STRING,所以我试过:
DATE_DIFF(IF(COUNT(DISTINCT hits.transaction.transactionId) > 0, PARSE_DATE('%Y%m%d',(MAX(DATE)),CURRENT_DATE())),CURRENT_DATE(),DAY) AS days_since_lst_ord
但后来我得到:
错误:参数类型的函数PARSE_DATE没有匹配的签名:STRING,STRING,DATE . 支持签名:[12:66] PARSE_DATE(STRING,STRING)
这是我的完整查询:
SELECT customDimension.value AS UserID, COUNT(DISTINCT VisitId) AS visits, COUNT(DISTINCT hits.transaction.transactionId) AS orders, IFNULL(SUM(totals.bounces),0) AS bounces, SUM(totals.pageviews) AS pageviews, IFNULL(SUM(hits.transaction.transactionRevenue)/1000000,0) AS revenue,
ROUND(IFNULL(COUNT(DISTINCT hits.transaction.transactionId)/COUNT(DISTINCT VisitId),0),5) AS conversion_rate,
ROUND(IFNULL(SUM(totals.bounces)/COUNT(DISTINCT VisitId),0),5) AS bounce_rate, ROUND(IFNULL(SUM(hits.transaction.transactionRevenue)/1000000,0)/COUNT(DISTINCT VisitId),2) AS rev_per_visit,
IF(COUNT(DISTINCT hits.transaction.transactionId) > 0,(MAX(DATE)),"unknown") AS last_ord_date,
DATE_DIFF(IF(COUNT(DISTINCT hits.transaction.transactionId) > 0, PARSE_DATE('%Y%m%d',(MAX(DATE)),CURRENT_DATE())),CURRENT_DATE(),DAY) AS days_since_lst_ord
FROM `PROJECTNAME.ga_sessions_20*` AS t
CROSS JOIN UNNEST (hits) AS hits
CROSS JOIN UNNEST(t.customdimensions) AS customDimension
WHERE parse_date('%y%m%d', _table_suffix) between
DATE_sub(current_date(), interval 1 day) and
DATE_sub(current_date(), interval 1 day)
AND customDimension.index = 2
AND customDimension.value NOT LIKE "true"
AND customDimension.value NOT LIKE "false"
AND customDimension.value NOT LIKE "undefined"
AND customDimension.value IS NOT NULL
GROUP BY UserID, hits.eventInfo.eventCategory
Order by orders DESC
2 回答
true_result和else_result表达式都必须能够强制转换为常见的超类型 .
例如,下面不起作用
而这一个 - 将完美地工作
请注意,最后一个陈述的结果将是1.0(FLOAT)
同时,如果你在下面运行 - 结果将是1(INTEGER)
查看更多关于Coercion的信息
你可以用这个替换
last_ord_date
和days_since_lst_ord
吗?