Looking Up Values in Tables or Dictionaries

Ignition
1

I'm trying to write a script that returns a weight given a part number. I calculate the weights as an average of the historic weights for the part number using SQL. This looks through a lot of data and takes along time. I have to call the averaged weight many times and don't want to run a SQL query every time. At this point I put the averaged weights and part numbers in a list using the queries and then loop through the list and return the averaged weight when the part number is equal to the part number I'm looking up. Is there a faster/more elegant solution to this? Ideally something like avWeight=myWeightTable['PartNumber'==1234].

2
  1. Please post your SQL query and format it properly using the </> button.
  2. Did you create an index on the "part number" column?

A named query should be something as simple as,

SELECT AVG(weight)
FROM myWeightTable
WHERE PartNumber = :partNum

Set the Named Query type to Scalor and it will return just the average value. No need for scripts, loops, or trouble.

3

Most database administrators would handle this with a DB-side tool called a "materialized view". You should ask your IT group for help with this, based on the DB brand.

1 Like
4

Sorry, I was unclear about what I'm trying to do here. At the start of my script I run a SQL query that selects the distinct part numbers. I then run a query for each part number that finds the average weight for each part number. I append the part number and averaged weight to a list. This goes through a lot of data and takes a long time to run. I don't want to run it again. I could turn this list into a table with headers or a dict if that would lead to a solution. After I make the list I then repeatedly look up parts and see how their weights compare to the average. I want to be able to take in the part number and return the average from my consolidated list. Something like this would be simple with pandas, but I'm not sure how to do it in Jython

5

You should still do this in SQL.

-- Assuming MySQL.
SELECT 
  t1.PartNumber, 
  t1.Weight, 
  t2.avgWeight, 
  t1.Weight - t2.avgWeight AS Diff 
FROM myWeightTable t1
LEFT JOIN (
  SELECT PartNumber, AVG(Weight) AS avgWeight
  FROM myWeightTable
  GROUP BY PartNumber
) t2
ON t1.PartNumber = t2.PartNumber;

Now you've got one dataset with the list of part numbers, weights, average weights and differences.

PartNumber Weight avgWeight Diff
5 22 22.5 -0.5
5 22.5 22.5 0
5 23 22.5 0.5
4 15 15.1999 -0.1999
4 15.2 15.1999 0
4 15.4 15.1999 0.1999
7 26 28 -2
7 28 28 0
7 30 28 2

You can try this out on FREE AI MySQL Online Compiler: Execute MySQL Query Online where I've created some sample data and the query.

(I notice you didn't supply either of the requested bits of information.)

6

This is screaming for attention on the DB side. A materialized view, or CTE or something. I wouldn't touch this with a script. This is the kind of thig that DB's are good for, let it do the work.

3 Likes