Need variables in parameterized view in clikhouse

Question

I need variable in parametrized view.

CREATE VIEW my_new_view AS
SELECT
CampaignName,
sentiment,    -- **CASE WHEN variable** = sentiment THEN sentiment ELSE NULL END AS sentiment_match
COUNT(CASE WHEN HOUR(StartTime) BETWEEN 23 AND 23 THEN sentiment ELSE NULL END) AS `23:00`
FROM call_details
WHERE voc.call_details.sentiment IN (
SELECT voc.call_details.sentiment
FROM voc.call_details
WHERE voc.call_details.sentiment = {sentiment:String}
)
GROUP BY CampaignName, sentiment --**in select if sentiment is there add sentiment in group also else dont add**
ORDER  BY CampaignName,sentiment  ASC;

need variable to store sentiment. is it possible in clikchouse, in mysql we can do by using stored procedure. in clickhouse how to do ?

Can you clarify what you're trying to achieve? Are you wanting the view to conditionally include the column sentiment in the query (both the SELECT and the GROUP BY) based on a variable being set to the value sentiment? — gingerwizard
– gingerwizard, Commented May 24, 2024 at 10:19

gingerwizard · Accepted Answer · 2024-05-24 10:40:44Z

0

I'm infering you want the view to conditionally include the column sentiment in the query (both the SELECT and the GROUP BY) based on a variable being set to the value "sentiment"?

Example below probably gets you most of the way there:


create table comments (sentiment String) ENGINE = MergeTree ORDER BY ()

INSERT INTO comments VALUES ('Positive'), ('Negative'), ('Positive'), ('Negative')

CREATE VIEW comments_view
AS SELECT
    if({column:String} = 'sentiment', sentiment, 'All') AS val,
    count()
FROM comments
GROUP BY val


SELECT *
FROM comments_view(column = 'anything')


   ┌─val─┬─count()─┐
   │ All │       4 │
   └─────┴─────────┘

1 row in set. Elapsed: 0.002 sec.

SELECT *
FROM comments_view(column = 'sentiment')

   ┌─val──────┬─count()─┐
   │ Positive │       2 │
   │ Negative │       2 │
   └──────────┴─────────┘

2 rows in set. Elapsed: 0.002 sec.

answered May 24, 2024 at 10:40

gingerwizard

5322 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dundi rajesh Over a year ago

here for SELECT * FROM comments_view(column = 'sentiment'); output is coming. but for SELECT * FROM comments_view(column = 'All'); it throwing error {DB::Exception: Column sentiment is not under aggregate function and not in GROUP BY}

gingerwizard Over a year ago

You need latest CH and experimental analyzer

Collectives™ on Stack Overflow

Need variables in parameterized view in clikhouse

I need variable in parametrized view.

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I need variable in parametrized view.

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related