Skip to content

Instantly share code, notes, and snippets.

@alexey-milovidov
Created July 22, 2016 00:03
Show Gist options
  • Save alexey-milovidov/2ebeb97a6dfe2e885e6cbae3bb2d7545 to your computer and use it in GitHub Desktop.
Save alexey-milovidov/2ebeb97a6dfe2e885e6cbae3bb2d7545 to your computer and use it in GitHub Desktop.
Example of emulation of rank function with arrayEnumerateUniq.
milovidov@milovidov-xenial:~$ clickhouse-client
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;
CREATE TABLE IF NOT EXISTS employee
(
empid Int32,
deptid Int32,
salary Int64
) ENGINE = Memory
Ok.
0 rows in set. Elapsed: 0.058 sec.
:)
:) 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);
INSERT INTO employee (empid, deptid, salary) VALUES
Ok.
:) SELECT * FROM employee ORDER BY deptid, salary DESC
SELECT *
FROM employee
ORDER BY
deptid ASC,
salary DESC
┌─empid─┬─deptid─┬───salary─┐
│ 1 │ 10 │ 5500000 │
│ 2 │ 10 │ 4500000 │
│ 4 │ 20 │ 4800000 │
│ 3 │ 20 │ 1900000 │
│ 7 │ 40 │ 44500000 │
│ 6 │ 40 │ 14500000 │
│ 5 │ 40 │ 6500000 │
│ 9 │ 50 │ 7500000 │
│ 8 │ 50 │ 6500000 │
└───────┴────────┴──────────┘
9 rows in set. Elapsed: 0.002 sec.
:) 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─┐
│ 1 │ 10 │ 5500000 │ 1 │
│ 2 │ 10 │ 4500000 │ 2 │
│ 4 │ 20 │ 4800000 │ 1 │
│ 3 │ 20 │ 1900000 │ 2 │
│ 7 │ 40 │ 44500000 │ 1 │
│ 6 │ 40 │ 14500000 │ 2 │
│ 5 │ 40 │ 6500000 │ 3 │
│ 9 │ 50 │ 7500000 │ 1 │
│ 8 │ 50 │ 6500000 │ 2 │
└───────┴────────┴──────────┴──────┘
9 rows in set. Elapsed: 0.004 sec.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment