Adding a column for Description based on tag value

Zach_Stewart · October 27, 2022, 12:46pm

I have been able to create a table with the help of the forum. It is able to count frequency and duration. I have two databases.

logs the machine state code with a t_stamp
Machine state with description

I am trying to get the table to replace or add column (not sure which would be more straightforward because I can hide the column) based on machine state code from the first table = description on the second table

I am able to write the basic INNER JOIN among the two tables, but I am struggling to find a way to join them in the existing query. I've tried adding the JOIN function between the subq and after, but I have not found luck.

I have the working INNER JOIN function in comments now for reference. Please do not hold back if there is a better way to do this that I am unaware of. Thank you in advance!!

Using PostgreSQL v15

SELECT "Machine_State_Code",

COUNT ("Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		FROM {machine_machinestatecode}
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
				
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM {machine_machinestatecode}
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
			
	) subq

WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

GROUP BY "Machine_State_Code"
ORDER BY count("Machine_State_Code") DESC

/*
SELECT *
FROM pou102_machinestatecode
  INNER JOIN pou102_descriptions ON pou102_machinestatecode."Machine_State_Code" =
    pou102_descriptions."State"
*/

SELECT *
FROM pou102_machinestatecode
  INNER JOIN pou102_descriptions ON pou102_machinestatecode."Machine_State_Code" =
    pou102_descriptions."State"

pturmel · October 27, 2022, 12:49pm

It is most efficient to perform that JOIN on as few rows as practical. At the last possible moment, so to speak. Wrap your query into another subquery, then join the final result to the descriptions. Consider using a LEFT JOIN, so that you won't skip codes that are missing a description.

Zach_Stewart · October 27, 2022, 1:48pm

Thank you for the suggestion!! That makes sense. I am receiving an error regarding subq."Machine_State_Code"
I've added that to the last GROUP BY, but it is not recognizing it. I don't believe I need to subq."Machine_State_Code" within the subq. Am I mistaken?

ERROR: column "subq.Machine_State_Code" must appear in the GROUP BY clause or be used in an aggregate function Position: 47

SELECT "Description", "State"
	
FROM(

SELECT subq."Machine_State_Code",

COUNT (subq."Machine_State_Code") AS "FREQUENCY",

SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"

FROM (
	SELECT 
	"t_stamp",
	"Machine_State_Code",
		coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
			CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END) AS t_stamp1
		FROM {machine_machinestatecode}
		WHERE "t_stamp" >= coalesce(( 
			SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
				
		AND "t_stamp" <= coalesce(( 
			SELECT "t_stamp" 
			FROM {machine_machinestatecode}
			WHERE "t_stamp" > :end_ts  
			ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
			
	) subq
	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

    ) subq1
	LEFT JOIN pou102_descriptions ON subq."Machine_State_Code" =
    pou102_descriptions."State"
    
GROUP BY subq."Machine_State_Code"
ORDER BY count(subq."Machine_State_Code") DESC

pturmel · October 27, 2022, 2:26pm

Use the subquery alias appropriate to the level of nesting. subq1."Machine_State_Code".

{Try to indent for each subq level.. it's had to track your syntax.}

Zach_Stewart · October 27, 2022, 3:13pm

Sorry about that! I am super new to this style of programming. I am receiving an error about missing a FROM-clause entry for table "subq1"

SELECT "Description", "State"
	
	FROM(

		SELECT "Machine_State_Code",
		COUNT ("Machine_State_Code") AS "FREQUENCY",
		SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"
		 
			FROM (
				SELECT "t_stamp", subq1."Machine_State_Code", coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
					CASE WHEN current_timestamp < :end_ts 
					THEN current_timestamp 
					ELSE :end_ts 
					END) AS t_stamp1
						FROM {machine_machinestatecode}
							WHERE "t_stamp" >= coalesce(( 
							SELECT "t_stamp" 
								FROM {machine_machinestatecode}
									WHERE "t_stamp" < :begin_ts 
									ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')
									AND "t_stamp" <= coalesce(( 
									SELECT "t_stamp" 
										FROM {machine_machinestatecode}
											WHERE "t_stamp" > :end_ts  
											ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
											
				) subq
			
	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	

    ) subq1

LEFT JOIN pou102_descriptions ON subq1."Machine_State_Code" = pou102_descriptions."State"
	 
GROUP BY subq1."Machine_State_Code"
ORDER BY count(subq1."Machine_State_Code") DESC

I've tried adding the FROM pou102_descriptions above and below the
WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts .
but only receive syntax errors

Zach_Stewart · October 27, 2022, 4:54pm

Working code. Thank you for the help @pturmel !!

SELECT 
	*
FROM (
	SELECT 
		subq."Machine_State_Code",
		COUNT (subq."Machine_State_Code") AS "FREQUENCY",
		SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"
	FROM (
		SELECT 
			"t_stamp",
			"Machine_State_Code",
			coalesce(LEAD("t_stamp") OVER (ORDER BY "t_stamp"), CASE WHEN current_timestamp < :end_ts THEN current_timestamp ELSE :end_ts END) AS t_stamp1
		FROM 
			{machine_machinestatecode}
		WHERE 
			"t_stamp" >= coalesce(( SELECT "t_stamp" FROM {machine_machinestatecode} WHERE "t_stamp" < :begin_ts ORDER BY "t_stamp" DESC LIMIT 1), 'Tue Oct 26 18:00:00 EDT 2021')				
				AND "t_stamp" <= coalesce((SELECT "t_stamp" FROM {machine_machinestatecode} WHERE "t_stamp" > :end_ts ORDER BY "t_stamp" DESC LIMIT 1), 'Fri Oct 26 18:00:00 EDT 2029')
		) subq
		
		WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts
		
		
	GROUP BY subq."Machine_State_Code"
	ORDER BY count(subq."Machine_State_Code") DESC 
	
	) subq1
	LEFT JOIN pou102_descriptions ON subq1."Machine_State_Code" = pou102_descriptions."State"

pturmel · October 27, 2022, 4:57pm

I noticed you are mixing value parameters (colon prefix) with querystring parameters (curly braces). You should change your named query to use only value parameters unless you have a very specific need for querystring parameters. And then go to the effort to audit all of the paths that supply data to querystrings that could be hacked.

Zach_Stewart · October 27, 2022, 6:00pm

Thank you for the suggestion. I will look into this. Last question (hopefully). Is there a straightforward way to reorganize the columns so that Description is first?
How it comes out after executing query:

How I would like it to come out:

Then I could just hide the additional columns in the table on the vision page.

pturmel · October 27, 2022, 6:02pm

The order of columns in the outermost SELECT list should be the order delivered.

Zach_Stewart · October 27, 2022, 6:03pm

Wow, I feel silly. A HUGE THANK YOU @pturmel

Transistor · October 27, 2022, 6:11pm

Perhaps this is a more readable format of your query - not that I've completely figured out what you're doing.

SELECT "Description", "State"
FROM(
	SELECT "Machine_State_Code",
	COUNT ("Machine_State_Code") AS "FREQUENCY",
	SUM(CASE WHEN t_stamp1 > :end_ts THEN :end_ts ELSE t_stamp1 END - CASE WHEN "t_stamp" < :begin_ts THEN :begin_ts ELSE "t_stamp" END) AS "DURATION"
	FROM (
		SELECT "t_stamp", subq1."Machine_State_Code", 
			COALESCE(
				LEAD("t_stamp") OVER (ORDER BY "t_stamp"),
				CASE WHEN current_timestamp < :end_ts 
				THEN current_timestamp 
				ELSE :end_ts 
				END
			) AS t_stamp1
		FROM {machine_machinestatecode}
		WHERE "t_stamp" >= COALESCE(
			( 
				SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" < :begin_ts 
				ORDER BY "t_stamp" DESC LIMIT 1
			)
			, 'Tue Oct 26 18:00:00 EDT 2021'
		)
		AND "t_stamp" <= COALESCE(
			( 
				SELECT "t_stamp" 
				FROM {machine_machinestatecode}
				WHERE "t_stamp" > :end_ts  
				ORDER BY "t_stamp" DESC 
				LIMIT 1
			), 
			'Fri Oct 26 18:00:00 EDT 2029'
		)
	) subq
	WHERE "t_stamp" < :end_ts AND t_stamp1 > :begin_ts	
) subq1

LEFT JOIN pou102_descriptions ON subq1."Machine_State_Code" = pou102_descriptions."State"
	 
GROUP BY subq1."Machine_State_Code"
ORDER BY COUNT(subq1."Machine_State_Code") DESC

It might make it easier for the next guy.

nminchin · October 28, 2022, 1:39pm

I can only imagine that those coalesces in your where clauses will be terrible for query performance, especially when you scale up the table size. You will be better off pulling that out and passing it in so you're just using a datetime in the where and no functions.

Edit: then again, maybe they're not so bad...