Common SQL Queries in Unify

Chris Parker

Hey Community, I'm curious: what are some of the common queries that you find yourself using frequently in Unify? Looking to compile a guide of the most common queries and some good tips--thanks!

kanyinsa

hey @Chris Parker great question. My favorite SQL query to run is using regexp_replace. This comes in handy when I want to remove a dash from the middle of a string, for example.

Let's say I start with a column called EquipmentID with the following data in it: C-4, C-16, C-10, etc. and I want to remove the delimiter, I would run this query below:

select *, regexp_replace(EquipmentID, '-',"") as NewEquipmentID from in

And the results would be a new column called NewEquipmentID with the following data in it: C4, C16, C10, etc.

Chris Parker

Thanks @kanyinsa! I am a fan of RegEx Replace too--especially to get rid of unwanted characters--thanks for sharing the syntax :-)

adam_element

Simple, but yet easy to forget - I like to use LOWER(), UPPER(), and TRIM() to clean up my datasets and normalize the output. It's especially useful right before a JOIN transformation!

dallas_element

Here's an advanced trick that I use to collapse a tall data table using the CONCAT_WS and COLLECT_LIST SQL functions. This is for cases where you want to collapse a table like this one:

Equipment  |  SensorName   | SensorTag
--------------------------------------
Pump101    |  Current      | IT1001.PV
Pump101    |  Temperature  | TT1001.PV
Pump102    |  Current      | IT1002.PV
Pump102    |  Temperature  | TT1002.PV

You can write a query to put all of the sensor tags into a single column as a comma-delimited list:

SELECT Equipment, 
       CONCAT_WS(',', COLLECT_LIST(SensorTag)) AS TagList 
FROM in 
GROUP BY Equipment

This will give the following output:

Equipment |  TagList
---------------------------------
Pump101   |  IT1001.PV,TT1001.PV
Pump102   |  IT1002.PV,TT1002.PV

That's it! If you want, you can then split by delimiter + rename columns and get a wide table. I don't use this function all the time, but it's been really useful when I've needed it.

Chris Parker

@adam_element those data cleansing operations should be a cardinal rule before you ever do a join! Thanks for sharing those, it's something I learned the hard way when I first got into analytics :-)

Chris Parker

@dallas_element I love it, that's similar to a crosstab function inside of Element. Super helpful for grouping things up, thanks for sharing!