This message was deleted.

Hi @Joshua Liguid, if the cardinality of the set of records you want to do the rank() over is not too high (i.e. in the few thousand or less) you should be able to do a semi-cartesian product by using a self join with an inequality. I'm an old Oracle hand and did this back in Oracle 5/6 prior to Windowing Functions. If you need more specifics on how to do this let me know and I can write it up later today. Otherwise please post what you come up with. Thanks. John

Hi John, appreciate the input, however I am looking to translate this query to be usable in Druid. Do you know any clever ways to get rank to work on Druid? If you this is what you meant, apologies for misunderstanding 😅

Hi Josh, yes that is what I meant, as a way of getting the rank of items in Druid SQL. Let me see if I can throw something together real quick as an example ...

Good news:

RANK

is coming to town:

@Joshua Liguid I believe this wikipedia example does what you are looking for:

SELECT * FROM
(
SELECT                     "countryName",
                    "page",
                    SUM ("added") count1,
                    SUM ("deleted") count2 
                FROM
                    "wikipedia_r" aa
                GROUP BY
                    1,
                    2
                ORDER BY 3 DESC
                LIMIT 10
)
UNION ALL
(
SELECT                     "countryName",
                    "page",
                    SUM ("added") count1,
                    SUM ("deleted") count2 
                FROM
                    "wikipedia_r" aa
                GROUP BY
                    1,
                    2
                ORDER BY 4 DESC
                LIMIT 10
)

The result is the list of 20 items that are either in the top 10 based on count1 or top 10 based on count2

Everyone, thank you for the suggestions! I will try to implement this afternoon and see what works out. @John Kowtko @Vadim @Sergio Ferragut

Good idea @Sergio Ferragut, if the rank # isn't needed in the output then your suggestion of using LIMIT clause looks like it would be far more efficient than a self-join.

It seems that rank # would be needed in my output, I am needing to get duplicate ranks as well

@Joshua Liguid, the self-join method will produce regular (non-Dense) rank numbers, since it is counting how many items have a value higher than it ... however in order to put the ties at the higher rank, you cannot use >= as the join condition anymore, you need to use >. Now you are faced with knocking out the top ranked item if you use a > as the join condition ... and since it looks like you can't use an inequality as an outer join predicate, you may have to count the rank in a filtered aggregate expression:

with tmp as (
 select channel column1, flags column2, sum(added) count1, sum(deleted) count2
   from "wikipedia"
  group by 1, 2
)
 select a.column1, a.column2, a.count1, a.count2,
        sum(case when b.count1 > a.count1 then 1 else 0 end)+1 "rank"
   from tmp a 
   JOIN tmp b ON b.column1 = a.column1 
  group by a.column1, a.column2, a.count1, a.count2
  order by 1, 5

This works for me and displays ties at the higher rank (e.g.1, 1, 3, 4, 5) instead of the lower rank (2, 2, 3, 4, 5) Yes, it will be a lot easier, and more readable, when the windowing functions are released 🙂

Okay, @John Kowtko, thank you for this example, let me run it and I’ll let you know, thank you!

one more attempt, this one should benefit from the early reduction in rows with the LIMITs and also provide the rank calculation based on both metrics. I couldn't get it to sort the final result without running into errors, but that should be simple on the client side.

with tmp1 as
(
  SELECT              "countryName",
                      "page",
                      SUM ("added") count1,
                      SUM ("deleted") count2 
                  FROM
                      "wikipedia_r" aa
                  GROUP BY
                      1,
                      2
                  ORDER BY 3 DESC
                  LIMIT 10
),
tmp2 AS
(
  SELECT              "countryName",
                      "page",
                      SUM ("added") count1,
                      SUM ("deleted") count2 
                  FROM
                      "wikipedia_r" aa
                  GROUP BY
                      1,
                      2
                  ORDER BY 4 DESC
                  LIMIT 10

)
select a."countryName", a."page", a.count1, a.count2
       ,sum(case when b.count1 > a.count1 then 1 else 0 end)+1 "rank"
  from tmp1 a
     , tmp1 b
  group by 1,2,3,4 
UNION ALL
select a."countryName", a."page", a.count1, a.count2
       ,sum(case when b.count2 > a.count2 then 1 else 0 end)+1 "rank"
  from tmp2 a
     , tmp2 b 
  group by 1,2,3,4

This will always keep the product join in the self-joins limited to 10x10 =100 rows, so it should be fast on the broker. It will not include more than 10 rows, in each ranking, so if there are ties, you will not get to rank=10. Rank function will be really nice to have. 😄