Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Example of emulation of rank function with arrayEnumerateUniq.
/*
ClickHouse client version 1.1.53996.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.53996.
*/
:) CREATE TABLE IF NOT EXISTS `employee` (
:-] `empid` Int32,
:-] `deptid` Int32,
:-] `salary` Int64
:-] ) ENGINE = Memory;
:) INSERT INTO `employee` (`empid`, `deptid`, `salary`) VALUES
:-] (1, 10, 5500000),
:-] (2, 10, 4500000),
:-] (3, 20, 1900000),
:-] (4, 20, 4800000),
:-] (5, 40, 6500000),
:-] (6, 40, 14500000),
:-] (7, 40, 44500000),
:-] (8, 50, 6500000),
:-] (9, 50, 7500000);
:) SELECT * FROM employee ORDER BY deptid, salary DESC
┌─empid─┬─deptid─┬───salary─┐
1105500000
2104500000
4204800000
3201900000
74044500000
64014500000
5406500000
9507500000
8506500000
└───────┴────────┴──────────┘
:) SELECT empid, deptid, salary, rank FROM (SELECT groupArray(empid) AS empids, groupArray(deptid) AS deptids, groupArray(salary) AS salaries FROM (SELECT * FROM employee ORDER BY deptid ASC, salary DESC)) ARRAY JOIN empids AS empid, deptids AS deptid, salaries AS salary, arrayEnumerateUniq(deptids) AS rank
SELECT
empid,
deptid,
salary,
rank
FROM
(
SELECT
groupArray(empid) AS empids,
groupArray(deptid) AS deptids,
groupArray(salary) AS salaries
FROM
(
SELECT *
FROM employee
ORDER BY
deptid ASC,
salary DESC
)
)
ARRAY JOIN
empids AS empid,
deptids AS deptid,
salaries AS salary,
arrayEnumerateUniq(deptids) AS rank
┌─empid─┬─deptid─┬───salary─┬─rank─┐
11055000001
21045000002
42048000001
32019000002
740445000001
640145000002
54065000003
95075000001
85065000002
└───────┴────────┴──────────┴──────┘
9 rows in set. Elapsed: 0.004 sec.
@kmatt
Copy link
Author

kmatt commented Nov 12, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment