Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
using System;
using System.Linq;
using System.Web.Http;
using BigQuery.Linq;
using SampleApp.BigQuery;
using SampleApp.BigQuery.Tables.Statistics;
using WebApi.OutputCache.V2;
namespace SampleApp.Admin.Controllers
{
public class UserActionSankeyDiagramApiController : SampleAppApiController
{
[HttpGet, CacheOutput(ClientTimeSpan = 86400, ServerTimeSpan = 86400)]
public object[][] Get(DateTimeOffset? targetMonth, int? targetStep)
{
var to = targetMonth.Value.AddDays(DateTime.DaysInMonth(targetMonth.Value.Year, targetMonth.Value.Month));
var context = BigQueryAccount.GetContext();
// 以下BigQueryへのクエリ文
var logs = context.From<ActionLog>("Statistics.ActionLog" + targetMonth.Value.ToString("yyyyMM"))
.Where(x => 1 <= x.Index && x.Index < targetStep.Value && targetMonth.Value <= x.Timestamp && x.Timestamp <= to)
.Select(x => new
{
x.Index,
FromPath = BqFunc.Concat(BqFunc.String(x.Index), "_", BqFunc.Substring(x.FromPath, 6)),
ToPath = BqFunc.Concat(BqFunc.String(x.Index + 1), "_", BqFunc.Substring(x.ToPath, 6)),
Count = BqFunc.Count()
})
.GroupBy(x => new { x.Index, x.FromPath, x.ToPath })
.OrderBy(x => new { x.Index, x.Count })
.ToArray()
// 上のToArrayで発行されたクエリの結果をGoogle chart toolsのSankey Diagramで扱いやすい形式に変換
.Select(x => new object[] { x.FromPath, x.ToPath, x.Count })
.ToArray();
return logs;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment