Clickhouse: [question] Best way to use "Dependent subqueries"

[RomanGorbatko]

Hi guys,

I wanna build the query to CH which looks like next MySQL example:

CREATE TABLE `test_views` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `page` varchar(11) DEFAULT NULL,
  `prev_page` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT;

INSERT INTO `test_views` (`id`, `page`, `prev_page`)
VALUES
    (1, 'page1', NULL),
    (2, 'page1', NULL),
    (3, 'page3', NULL),
    (4, 'page4', 'page3'),
    (5, 'page3', NULL),
    (7, 'page3', NULL);


select count(v.page) as current, v.page,
    (
        select count(sq.page) from test_views as sq where sq.prev_page = v.page
    ) as next
from test_views as v
group by v.page

Output should be:

(`current`, `page`, `next`)

    (2, 'page1', 0),
    (3, 'page3', 1),
    (1, 'page4', 0),

I know what CH doesnt support Dependent subqueries like sq.prev_page = v.page but i cant found best solutions for this case

[blinkov]

@RomanGorbatko you could implement this by explicitly JOINing these subqueries

[RomanGorbatko]

@RomanGorbatko you could implement this by explicitly JOINing these subqueries

As i know i cant use mysql-like syntax around JOIN.

So, i cant do something like this:

select 
    count(v.Page) as current,
    v.Page,
    count(next)
from 
    test as v
join test as next on next.PrevPage = v.Page
group by v.Page

... coz CH doesn't procces ON statement.

Also, i try to use CH JOIN syntax:

select 
    count(v.Page) as current,
    v.Page
from 
    test as v
inner join (select next.PrevPage as next  from test as c where c.Page = v.Page) using next
group by v.Page

... and i have Dependent subqueries problem: Unknown identifier: v.Page

[blinkov]

Recent ClickHouse releases support ON and do not require subquery on right side, though there are still some known issues with full column names.

[RomanGorbatko]

@blinkov

sounds nice :)

I wrote next query:

select 
    count(v.Page) as current,
    v.Page,
    if(next, 1, 0)
from 
    test as v
any left join test as next on next.PrevPage = v.Page

group by v.Page

Looks like the best variation.

But I have column name error as u say: Unknown identifier: next.
So, any ideas to fix this issue?

[blinkov]

@RomanGorbatko if(next, 1, 0) — next is whole table here, not some valid expression. Also count(v.Page) given there's GROUP BY same column doesn't make much sense.

[4ertus2]

6697