合并日期，hits.hour&命中。分钟字段 [英] Combine date, hits.hour & hits.minute field

问题描述

我想结合日期，hits.hour&将hit.minute字段合并到一个日期字段中，Tableau可以轻松读取这些日期字段以进行可视化。

目前日期是一个字符串，hits.hour& hits.minute是整数。

最后，日期应该是YYYY-MM-DD& hh：mm。

Atm我将日期转换为字符串，但我不知道如何合并现在的字段，或者一般情况下如果第一步是正确的话。

Best，
Pascal

  SELECT 
 fullVisitorId，
 visitId，
日期，
 CAST（hits.hour as string）AS小时，
 CAST（hits.minute as string）AS分钟，
总计.transactions，
 hits.item.productName，
 FROM [X.ga_sessions_20180221] 
 WHERE hits.hitNumber = 1 
 GROUP BY 
 fullVisitorId，visitId，date，hour ，minute，hits.item.productName，totals.transactions 
  

解决方案

您只需要连接选定的值

  SELECT 
 fullVisitorId，
 visitId，
 CAST （日期为字符串）+'& '+ CAST（hits.hour as string）+'：'+ CAST（hits.minute as string）AS datetime，
 totals.transactions，
 hits.item.productName，
 FROM [ X.ga_sessions_20180221] 
 WHERE 
 hits.hitNumber = 1 
 GROUP BY 
 fullVisitorId，visitId，date，hour，minute，hits.item.productName，totals.transactions 
  

I would like to combine the date, hits.hour & hits.minute field into one date-field that can easily be read by Tableau for visualization.

At the moment the date is a string and the hits.hour & hits.minute are integers.

In the end the date should be something like YYYY-MM-DD & hh:mm.

Atm I converted the date to a string but I dont know how to combine the fields now, or in general if this first step was correct.

Best, Pascal

SELECT 
fullVisitorId,
visitId,
date,
CAST (hits.hour as string) AS hour,
CAST (hits.minute as string) AS minute,
totals.transactions ,
hits.item.productName,
FROM [X.ga_sessions_20180221] 
WHERE hits.hitNumber =1
GROUP BY 
  fullVisitorId, visitId, date, hour, minute, hits.item.productName, totals.transactions

解决方案

You just need to concatenate selected values

SELECT 
    fullVisitorId,
    visitId,
    CAST(date as string) +' & ' + CAST (hits.hour as string) + ':' + CAST (hits.minute as string) AS datetime,
    totals.transactions ,
    hits.item.productName,
FROM [X.ga_sessions_20180221] 
WHERE 
    hits.hitNumber =1
GROUP BY 
  fullVisitorId, visitId, date, hour, minute, hits.item.productName, totals.transactions

这篇关于合并日期，hits.hour&命中。分钟字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！