Add row with variable name in Kusto

0
votes

I have a database with a lot of rows which contains the time when a fruit is sold in which shop, such as : 

timestamp             | shop | fruit
01/01/2019 3:00:00 AM | A    | Apple
01/01/2019 3:03:00 AM | A    | Apple
01/01/2019 3:05:00 AM | A    | Pear
01/01/2019 4:00:00 AM | B    | Apple

I would like to create in Kusto a new table which count the number of type of fruits sold in each shop within 1 hour (a fixed-width bins datetime) such that I have : 

timestamp             | shop | count
01/01/2019 3:00:00 AM | A    | 2
01/01/2019 3:00:00 AM | B    | 0
01/01/2019 4:00:00 AM | A    | 0
01/01/2019 4:00:00 AM | B    | 1

I tried to introduce null bins with this code:

TableRaw
| summarize count=dcount(fruit) by bin(timestamp, 1h), shop
| union (
  range x from 1 to 1 step 1
  | mv-expand timestamp=range(StartTime, StopTime, 1h) to typeof(datetime)
  | extend count=0
  )
| summarize count=sum(count) by bin(timestamp, 1h), shop

But in the output, I do have the raw with count = 0 but without the shop name.

I know that I can filter by shop then add the null bins and join all the tables together but I don't want to do that by hand since in the futur I may have new shops coming in my TableRaw with a random name.

I thought, maybe I can have a list of all the shops name with make-setand looping on it, but I don't think it is possible to do a loop in Kusto ... (I am really new in Kusto)

2
azure-data-explorer

2 Answers

2
votes

you should be able to use make-series:

let T = datatable(timestamp:datetime, shop:string, fruit:string)
[
    datetime(01/01/2019 3:00:00 AM), 'A', 'Apple',
    datetime(01/01/2019 3:05:00 AM), 'A', 'Pear',
    datetime(01/01/2019 4:00:00 AM), 'B', 'Apple',
];
let start = toscalar(T | summarize min(timestamp));
let end = toscalar(T | summarize max(timestamp)) + 1tick;
T
| make-series dcount(fruit) on timestamp from start to end step 1h by shop
| mv-expand dcount_fruit to typeof(long), timestamp to typeof(datetime)
| project timestamp, shop, dcount_fruit
0
votes

Ok, So I manage to resolve my problem. I don't know if it's the cleanest way to do that but I'll post my code in case someone will face the same issue :

// Get the list of shop name
let shopname = TableRaw
| summarize list_name = make_set(shop);
let a = tostring(toscalar(shopname));

TableRaw
| summarize count=dcount(fruit) by bin(timestamp, 1h), shop
| union (
  range x from 1 to 1 step 1
  | mv-expand timestamp=range(StartTime, StopTime, 1h) to typeof(datetime)
  | mv-expand shop= parse_json(a) to typeof(string)
  | extend count=0
  )
| summarize count=sum(count) by bin(timestamp, 1h), shop