Please Sign In or Register to join the Community to see more content!

Common SQL Queries in Unify

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
    kanyinsa Alumni Posts: 53

    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
    Chris Parker Alumni Posts: 9

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

  • adam_element
    adam_element Alumni Posts: 9

    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
    dallas_element Alumni Posts: 3

    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
    Chris Parker Alumni Posts: 9

    @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
    Chris Parker Alumni Posts: 9

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