UserActionSankeyDiagramApiController.cs

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;
        }
    }
}