I want to get unique customer counts. I have reference of postgresql query. Could you please convert this query into HiveSql Answer Use case expressions: One more method for counting distinct is size(collect_set()):
Tag: postgresql
How to use over – partition by query in SQL in order to get the current, average, and maximum value?
I have this table where it shows a point that was being done by a device at an area and specific location. I want to get the current, average and also maximum points grouped by area and also location. If I choose any day, the current qty will show the quantity of the latest working date. Meanwhile, average qu…
Convert Postgresql into HiveSql
How do I convert below mentioned postgresql query into HiveSql Answer Use CASE expressions like this: Also in Hive version >= 1.3 you can use quarter function:
PostgreSQL equivalent of Pandas outer merge
I am trying to do in Postgres the equivalent of Pandas outer merge, in order to outer merge two tables. Table df_1 contains these data: Table df_2 contains these data: So Table df_1 has one extra column (random_id) than df_2. Also, job_id 1711418 and worker_id 45430 exist in both df_1 and df_2. If I use the &…
Is there any query to find duplicate records count corresponding to package_no column?
I have a table structure like this I Need to count duplicate road code corresponding to the column package_no I expect one record from the above table with a duplicate package p1 and road count with 1 Answer You have a consistency problem. In your initial description you say “duplicate road code corresp…
PostgreSQL query update returns UPDATE 0
I am having trouble with a PostgreSQL query where my update condition contains more prerequisites. I attached 2 images to describe my problem. I want to update the highlighted row But the response is: Answer Your problem might be somewhere else (see this db<>fiddle) A few thoughts on your data: Reduce t…
Transform JSON rows into PostgreSQL Table
I have a table in my database that returns me some columns. One of them is a list of JSONs that are, in fact, other tables, but in JSON format. The image belows show an example. I want to transform each row of that column “data” into separate tables, because I need specific information inside each…
Convert array of timestamps in PostgreSQL (not in a table)
I need to get this command: to work for an array (or in rows). Reason is that I have a lot of timestamps that need to be converted and if I use the above structure, I just end up adding lots of columns that my front-end can’t handle. My current solution is to actually dump all of the timestamps into
Select one existing row order by where clause
I have a table like that: ID | lang ——— 1 | ru 2 | en I want to select 1 top existing row from my where clause. Not ordering by id or by lang, but ordering by my where clause. where lang in (‘…
PostgreSQL add new not null column and fill with ids from insert statement
I´ve got 2 tables. The tables are already filled with a lot of data. Now I want to add a new column content_id (NOT NULL) to the data table. It should be a foreign key to the content table. Is it possible to automatically create an entry in the content table to set a content_id in the data table. For