I am wondering if it is possible to achieve following similar result by running a groupBy query :

[
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "polygon",
        "total": 197944
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "ethereum",
        "total": 1994412
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "celo",
        "total": 1234
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "All Others",
        "total": 123
    },
    .
    .
    .
    {
        "ts": "2024-04-30T11:00:00.000Z",
        "network": "polygon",
        "total": 193953
    },
    {
        "ts": "2024-04-30T11:00:00.000Z",
        "network": "ethereum",
        "total": 19393
    },
    {
        "ts": "2024-04-30T11:00:00.000Z",
        "network": "celo",
        "total": 111
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "All Others",
        "total": 11
    },
]

I am particularly interested in whether we can get the

All Others

by just showing the top3 or top4 and then all others get aggregated into that field

Are the top3/4 predefined fields that you wanna separate from the ‘All Others’? If so, you can have a virtual column (CASE..WHEN in SQL) that maps polygon, ethereum, celo to themselves, and rest of the fields to ‘All others’ and group on that column

Not really predefined, they can actually be some other network, that example payload does not mean we will always have the Polygon, Ethereum and Celo in top3, they could be something else too

by top, do you mean those columns with max “total” count?

yes, it could be

{
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "A",
        "total": 197944
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "B",
        "total": 1994412
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "C",
        "total": 1234
    },
    {
        "ts": "2024-04-29T12:00:00.000Z",
        "network": "All Others",
        "total": 123
    },

but always with a determined info showing the top3 or the top4 and everything else in

All Others

. The idea is to render a stacked bar chart where each bar will be shown for each timestamp

the simplest way i can think of is to do 2 queries: one topN style (get the top 3) and another timeseries style (get the total) and then compute

All Others

on the client side by subtraction i'm sure there's a fancier way, that would actually produce a result from a single query like the one you mentioned, involving window functions. something like the following. however note that it does require window fns (currently experimental) and will also require loading up all of the distinct

countryName

in memory in order to compute the ranks and re-group the "All others". so it will have limitations if there's a lot of distinct

countryName

. (which there aren't, because there aren't that many countries, but there may be a lot of

network

in your example)

select
  case when least(rnk, 4) < 4 then countryName else 'All others' end as Country,
  sum("cnt") as "cnt"
from (
  select
    rank() over (order by count(*) desc) rnk,
    countryName,
    count(*) as "cnt"
  from wikipedia
  where countryName is not null
  group by countryName
)
group by 1, least(rnk, 4)
order by least(rnk, 4)

hmm I see that would be tricky, obviously I do not think there would be many networks in our case tbh, it pretty much depends also on what the user’s requests are (basically we store user’s requests to our API), from all the networks available through our API, user might only submit requests for 6 or 7 of them. Is there a native json druid query similar to that with window fns?

yep, there always is. you can do

explain plan for

on the SQL query to see what the native query would be

Thanks Gian. I got the native one but have a question:

{
  "queryType": "groupBy",
  "dataSource": {
    "type": "query",
    "query": {
      "queryType": "windowOperator",
      "dataSource": {
        "type": "query",
        "query": {
          "queryType": "groupBy",
          "dataSource": {
            "type": "table",
            "name": "test_dev"
          },
          "intervals": {
            "type": "intervals",
            "intervals": [
              "2024-05-08T12:00:00.000Z/2024-05-09T12:00:00.001Z"
            ]
          },
          "filter": {
            "type": "and",
            "fields": [
              {
                "type": "equals",
                "column": "user_id",
                "matchValueType": "STRING",
                "matchValue": "1234abcd"
              },
              {
                "type": "not",
                "field": {
                  "type": "null",
                  "column": "network"
                }
              }
            ]
          },
          "granularity": {
            "type": "all"
          },
          "dimensions": [
            {
              "type": "default",
              "dimension": "network",
              "outputName": "d0",
              "outputType": "STRING"
            }
          ],
          "aggregations": [
            {
              "type": "count",
              "name": "a0"
            }
          ],
          "limitSpec": {
            "type": "NoopLimitSpec"
          },
          "context": {
            "enableWindowing": true,
            "queryId": "f4b80893-f3ba-4a18-be45-a04ad3e3854a",
            "sqlOuterLimit": 1001,
            "sqlQueryId": "f4b80893-f3ba-4a18-be45-a04ad3e3854a",
            "useNativeQueryExplain": true
          }
        }
      },
      "intervals": {
        "type": "LegacySegmentSpec",
        "intervals": [
          "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
        ]
      },
      "context": {
        "enableWindowing": true,
        "queryId": "f4b80893-f3ba-4a18-be45-a04ad3e3854a",
        "sqlOuterLimit": 1001,
        "sqlQueryId": "f4b80893-f3ba-4a18-be45-a04ad3e3854a",
        "useNativeQueryExplain": true
      },
      "outputSignature": [
        {
          "name": "d0",
          "type": "STRING"
        },
        {
          "name": "a0",
          "type": "LONG"
        },
        {
          "name": "w0",
          "type": "LONG"
        }
      ],
      "operatorDefinition": [
        {
          "type": "naiveSort",
          "columns": [
            {
              "column": "a0",
              "direction": "DESC"
            }
          ]
        },
        {
          "type": "naivePartition",
          "partitionColumns": []
        },
        {
          "type": "window",
          "processor": {
            "type": "rank",
            "group": [
              "a0"
            ],
            "outputColumn": "w0",
            "asPercent": false
          }
        }
      ],
      "leafOperators": [],
      "granularity": {
        "type": "all"
      }
    }
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "virtualColumns": [
    {
      "type": "expression",
      "name": "v0",
      "expression": "case_searched((least(\"w0\",4) < 4),\"d0\",'All others')",
      "outputType": "STRING"
    },
    {
      "type": "expression",
      "name": "v1",
      "expression": "least(\"w0\",4)",
      "outputType": "LONG"
    }
  ],
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "default",
      "dimension": "v0",
      "outputName": "_d0",
      "outputType": "STRING"
    },
    {
      "type": "default",
      "dimension": "v1",
      "outputName": "_d1",
      "outputType": "LONG"
    }
  ],
  "aggregations": [
    {
      "type": "longSum",
      "name": "_a0",
      "fieldName": "a0"
    }
  ],
  "limitSpec": {
    "type": "default",
    "columns": [
      {
        "dimension": "_d1",
        "direction": "ascending",
        "dimensionOrder": {
          "type": "numeric"
        }
      }
    ],
    "limit": 1001
  },
  "context": {
    "enableWindowing": true,
    "queryId": "f4b80893-f3ba-4a18-be45-a04ad3e3854a",
    "sqlOuterLimit": 1001,
    "sqlQueryId": "f4b80893-f3ba-4a18-be45-a04ad3e3854a",
    "useNativeQueryExplain": true
  }
}

I am not sure where this intervals comes from:

-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z

. If I change them for

2024-05-08T12:00:00.000Z/2024-05-09T12:00:00.001Z

then I get null pointer but I found out it is because I need to change the granularity from:

"granularity": {
    "type": "all"
  },

to:

"granularity": {
    "type": "period",
    "period": "PT1H",
    "timeZone": "Etc/UTC"
  },

however with above changes I get nothing. In order to get results I need to keep granularity type

all

and

-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z

intervals. The SQL query:

select
  case when least(rnk, 4) < 4 then network else 'All others' end as Network,
  sum("cnt") as "cnt"
from (
  select
    rank() over (order by count(*) desc) rnk,
    network,
    count(*) as "cnt"
  from "test_dev"
  where "network" is not null and "user_id" = '1234abcd' and __time BETWEEN TIMESTAMP '2024-05-08 12:00:00' AND TIMESTAMP '2024-05-09 12:00:00'
  group by network
)
group by 1, least(rnk, 4)
order by least(rnk, 4)

as to the intervals

-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z

, that's just because there is no time filter. It's how we represent a SQL query with no time filter in a native query [where time filter is required] -- min possible timestamp to max possible timestamp

When you refer to time filter in the SQL, you meant a SQL like this?:

select
  time_hour,
  case when least(rnk, 4) < 4 then network else 'All others' end as network,
  sum("total") as "total"
from (
  select
    TIME_FLOOR(__time, 'PT1H') as time_hour,
    rank() over (order by count(*) desc) rnk,
    network,
    count(*) as "total"
  from "test_dev"
  where "network" is not null and "user_id" = '1234' and __time BETWEEN TIMESTAMP '2024-05-08 12:00:00' AND TIMESTAMP '2024-05-09 12:00:00'
  group by network, TIME_FLOOR(__time, 'PT1H')
)
group by time_hour, 2, least(rnk, 2)
order by time_hour, network asc

Above I am specifying a time filter and I am getting still in the middle of the json query

"intervals": {
        "type": "LegacySegmentSpec",
        "intervals": [
          "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
        ]
      },

and near the bottom

"intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },

i do mean a sql like that yeah

all right, however it we were going to generate this json native queries by code, guessing the other intervals in the subqueries might be tricky

got it, that makes sense

-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z

is kind of like a special interval that means "don't time filter". in our SQL planner code it's a constant

Intervals.ETERNITY

. if you are writing native queries directly you can use it anytime a query or subquery doesn't need to filter what's below it in most cases a more-specific time filter is only needed on the lowest-level query that hits the table directly

all right, got it, I can incorporate that into the library, now looking into the code base for druid makes sense

i thought with that comment you were saying that you want to skip

All Others

if the # of networks returned is less than the rank threshold you'd defined? i believe that should happen "automatically" since the

case

statement will never return

All Others

if no network has a rank greater than the number in there

well it is imposible that it happens dynamically I guess

are you actually seeing that kind of problem? i think you wouldn't, since the rank is computed based on the result set if a result set for customer

1234

has only 2 networks then one will have rank

1

and one will have rank

2

But shouldn’t not we take into account that rank happens over a window? That’s it the PT1H? Data ingested for that data source has 1 minute granularity and aggregated on some fields. So taking into account a user might hit our API at high request per second, then over a minute we have quite a lot of data for different networks. If then the query we do is for PT1H, the rank for a network on a given 1H window is higher than 1 if there are more than one request right? That why we see a number like 46 for some network on a given hour and different rank number for that same network on different hour

ah, i missed that you didn't just want a single list of networks, you want a list per hour or some other time period

I guess the

All Others

should be determined per time period right? but I though we were doing so with following query

select
    TIME_FLOOR(__time, 'PT1H') as time_hour,
    RANK() over (order by count(*) desc) as rnk,
    network,
    count(*) as "total"
  from "monopipe_dev"
  where "network" is not null and "user_id" = 'c2d6c42a8b0b4da4816cdad307a54850' and __time BETWEEN TIMESTAMP '2024-05-08 12:00:00' AND TIMESTAMP '2024-05-09 12:00:00'
  group by network, TIME_FLOOR(__time, 'PT1H')
  order by 1 asc

or do we somehow have to include the time period within

RANK() over …

?

oh, yeah, that's SQL layer magic. in native queries we typically process timestamps as milliseconds since epoch; the SQL layer pretty-prints them when it sends them back to you

I thought of processing the query result in code and format it to human readable format.

i think for

yyyy-MM-ddTHH:mm:ssZ

you need to escape the T and Z

yep, completely right! had to scape T and Z, thanks!

wondering if you did end up figuring this out? i was too busy last week to check slack much

Yeah I think we got it right, however we were not sure if we should have the rack per bucket window or whole period, so in above query I have

RANK() over (PARTITION by TIME_FLOOR(__time, 'PT1H') order by count(*) desc) as rnk,

in the inner query, giving it for each 1h bucket in the whole 24h period, but not sure if we should have the

All Others

aggregated in the 24h period instead of 1h bucket windows, an not sure if possible at all