I am facing a problem while getting the cumulative distinct count of resource ids as of different modified dates in vertica. If you see the below table I have resource id, modified date and deleted date and I want to calculate the count of distinct active resources as of all unique modified dates. A resource is considered active when deleted
Tag: vertica
PostgreSQL -Assign Unique ID to each groups of records, based on a condition
I am trying to group records of a table, based on a condition, and then asing in a new column a simple integer ID for each group obtained from where the condition is met. ID TMSTP CAT_TYPE TELEGRAMMZAEHLER 1 2022-05-03 20:52:02 32 5004 2 2022-05-03 20:51:34 32 5002 3 2022-05-03 20:51:34 32 5001 4 2022-05-03 20:51:33 32 5000 5 2022-05-03
REGEXP_SUBSTR to extract a substring from Vertical SQL
I am trying to run the below query to get a substring from a string but it gives wrong result. I am not sure how to use the regex since the regex I am using works well with PCRE. I am expecting to get ndehqb-a3g4-a10 in this case but it prints the original string itself. The ask is to get
Calculating the moving average in Vertica for all stocks
I have a stocks table for example: I want to calculate moving average for all the stocks with a single query in Vertica. This is the way to do so for only one stock (according to Vertica website): Output: If I try to select all the stock: I am getting a wrong result. How can I fix that? Answer Add
SQL to find sum of total days in a window for a series of changes
Following is the table: start_date recorded_date id 2021-11-10 2021-11-01 1a 2021-11-08 2021-11-02 1a 2021-11-11 2021-11-03 1a 2021-11-10 2021-11-04 1a 2021-11-10 2021-11-05 1a I need a query to find the total day changes in aggregate for a given id. In this case, it changed from 10th Nov to 8th Nov so 2 days, then again from 8th to 11th Nov so
Error while running very long SQL query with UNION ALL operator in Vertica
This is a sample of subquery used in Vertica Query which is a string generated from the application. There are some cases when this subquery becomes huge as the number of rows with UNION ALL increases. Vertica supports this query to some extent. But when there is let’s say 3000 + UNION ALL operator used in a subquery it throws
SQL: Get row number which increases every time a value changes
I have the following table in Vertica: The table is ordered by column_1 and column_3. I would like to add a row number, which increases every time when column_1 or column_3 change their value. It would look something like this: I tried using partition over but I can’t find the right syntax. Answer Vertica has the CONDITIONAL_CHANGE_EVENT() analytic functions. It
Median of three columns (for each row) in Vertica SQL
My table looks like this: Product Length Width Height A 34 22 10 B 40 2 12 I would like to get the longest, second longest and third longest side for each product. For the longest and third longest it was easy, I did it like with GREATEST() and LEAST(). For the second longest for the moment I am doing
How to add time column to the time stamp column in Vertica ? (or another db’s)
I have two columns like that: ihour (type=HH:mm:ss) 06:40:41 its (type=timestamp) 2020-12-10 00:00:00.000000 I want to add ihour to the its column and have a single column showing me date and time. How can I do this in Vertica? If you know how to do it in another db’s, I would be happy because at least I could learn the
Convert day of week name to day of week number Vertica
With this way – SELECT TO_CHAR(NOW(), ‘DAY’); I can get a day of week name(E.G. MONDAY). With SELECT dayofweek(NOW()); I can get the number of the day of week. (E.G 1) QUESTION: How to convert day of week name to day of week number. (E.G MONDAY -> 1) SMTH like TO_NUMBER(‘MONDAY’, ‘DAY’); –> 1 (just for example, don’t working) Answer