I am trying to find a way to split my array when the next element is smaller than the previous element. Here is some sample data [100, 200, 500, 100, 150, 200] I need to convert this to [[100, 200, 500],[100, 150, 200]] I am trying to calculate the running difference of the array and I need to handle the
Tag: clickhouse
Is there a way to calculate difference whthin a specific time range using clickhouse MV?
For example, this is my source table What I want to do is to create a MV which asynchronously calculate the price difference whthin 30 min. And then, I can get the difference from this MV using an id every time. Here is an SQL example. It works correctly while directly execute it, but does not work while crea…
Nearest matching text search using the same table in ClickHouse
Insert Data I want get the nearest matching text for each path field using the same table I read this example, but in this case “clickhouse” isn’t a value of the same table… Answer You do understand that it’s a cross join and in case of 10 rows, it’s 100 ngramDistance calls…
How to classify rows having one or more related records
I need to add the additional column called rank_2 which will show whether the source has more than 1 source_ids. So, I need to get this result: I don’t know how to write a query to get rank_2, which will contain 1 when the source has the only source_id (like the source “b”) and contain 2 whe…
I am getting this error on inserting data in ClickHouse
Unknown error field: Code: 27. DB::ParsingException: Cannot parse input: expected ‘”‘ before: ’40”,”gmtoffset”:0,”open”:109.6,”high”:109.6,”low”:109.6,”close”:109.6,”volume”:0,”previousClose”:108.…
How to select data with percentages from Clickhouse?
Given the following table: I’m trying to fetch all statuses by host, with their relative percentage. In order to do that, I need to count results grouped by host and status, and divide each counted field by its total host fields count. For example, this query would have worked in MySQL: But ClickHouse t…
Clickhouse; Issues listing ip addresses where the column has multiple ip addresses
I am trying to get a list of all of our users’ IPv4 addresses in a Clickhouse Table. Since some of our users have multiple IPv4 addresses separated by a comma, I created this query: but it gives me this error: Nested type Array(String) cannot be inside Nullable type. is it possible to only select a port…
How to parse a Clickhouse-SQL statement using ANTRL4?
Objective : Add an additional WHERE clause to any given Clickhouse statement. I’m using the following Antlr grammars to generate Java classes for a lexer & parser. Lexer grammar https://github.com/ClickHouse/ClickHouse/blob/master/utils/antlr/ClickHouseLexer.g4 Parser grammar https://github.com/Clic…
Clickhouse: Mapping BETWEEN filtering from an array
I understand if I want to filter a column between two numbers I can use BETWEEN: Is there a way of mapping the filtering to an array of values, for instance, if the array was [1, 10, … , N]: Answer Try this query:
Analysis of the completeness of the availability of products from the ClickHouse SQL database
Documentation: https://clickhouse.tech/docs/en/ Goal: 85% of the brand’s product range should be available for purchase Calculate the number of products of each brand by availability (maxItems > 0)…