1

I've done my best to strip down code to bare minimum and provided concise explanation of problem (I hope)

I have a db scheme as indicated below.

def a_method(info_hash)
     ...
     db.execute2 "create table if not exists Table1(Id INTEGER PRIMARY KEY, Item TEXT, Amount FLOAT, Type TEXT, AvgInc FLOAT, AvgX FLOAT, Total FLOAT)"
     ...
end

This creates my schema. Once I populate the database, I run the following method which gives me the #{@total} value grouped by my Type column:

def get_total(info_hash)
    ...
    get_amt =  db.execute2 "SELECT sum(Amount) from Table1 WHERE Type = :Type", info_hash[:category]
    puts "foo"
    db.execute2 "UPDATE Table1 SET Total = :Total WHERE Type = :Type", get_amt[1][0], info_hash[:category]
    @total=get_amt[1][0]
    ...
    @total
end

I want to run the following method to compute an Item average in the event the item did not exist. Basically the impact each Item has on the Type average.

Please see the in code comment for where I think my logic is faulty

def method_excluding(info_hash) 
     ...
     get_num =  db.execute2 "SELECT Amount from Table1 WHERE Type = :Type", info_hash[:category]
    i = 0
    get_num.each do |item| 
    #purpose of block to compute an average value for Type as if #{item} did not exist.  So: the average value for Type EXLCUDING #{item}
        if i == 1
            @avgx = (@total - get_num[1][0]) / i
        elsif i > 1
            @avgx = (@total - get_num[i][0]) / (i - 1)
        end         
        i+=1
        db.execute2 "UPDATE Table1 SET AvgX = :AvgX WHERE Type = :Type", @avgx, info_hash[:category]
        ...
end

Please advise on how I can get to my desired outcome.

foo
  • 195
  • 1
  • 8
  • 1
    Instead of doing two queries in your `get_total`, which is subject to [Race Conditions](https://en.wikipedia.org/wiki/Race_condition), do one with an `UPDATE` [using a `JOIN`](https://stackoverflow.com/questions/19270259/update-with-join-in-sqlite). An atomic update won't expose you to situations where the total computes incorrectly. – tadman Apr 09 '19 at 01:16
  • 1
    If you want a counter, consider `get_num.each_with_index do |item, i|` where `i` is a 0-indexed counter provided automatically. There's no need for a separate test. Also **`i=1` is an assignment, not a comparison**. For that you need `==`. It's worth noting that your first iteration will not match either `if` branch when `i == 0`. – tadman Apr 09 '19 at 01:17
  • @tadman it's not that I WANT a counter...I need to know the total `Type` I have to compute an average. How can I do this w/o the counter? – foo Apr 09 '19 at 01:35
  • @tadman can u show me an example of `join`. Reading up now, I don't think it pertains for this specific use case...`join` essentially is used when having 2 different tables, not 1. Also, can there be situations where `total` computes incorrectly? – foo Apr 09 '19 at 01:41
  • `JOIN` is precisely what you use when you have two different tables. It's how you relate them together in a single query. – tadman Apr 09 '19 at 01:42
  • @tadman yes. but I only have myTable...and column for `total` – foo Apr 09 '19 at 01:46
  • The name `myTable` is really annoyingly vague here, and it's not clear why you're calling `SUM(Amount)` on a table and then updating the table with that sum. Won't that increment infinitely, as the future sum is now higher? A slightly more concrete use case would help considerably here. – tadman Apr 09 '19 at 01:47
  • Every time you update your table with a new value it updates *every* matching row. Do you really want to do that? And why save these values at all? It's trivial to compute them on the fly when needed... – Shawn Apr 09 '19 at 02:00
  • @tadman apologies renamed table for clarification. Pls understand `@total` will populate its own column. So it won't increment infinitely... – foo Apr 09 '19 at 02:03
  • @Shawn it's more to have `@total` always on hand...user is able to display each row...so they can see what the total is as well. – foo Apr 09 '19 at 02:05
  • `myTable` and `table1` are two flavours of the same. Is this a transactions table? A balances table? What's going on in there? You sum all amounts for a single category, then *update all entries in the database for that category* with that total. If there's two or more rows per category, this keeps incrementing forever. If there's one row per category then you don't need `SUM()`. – tadman Apr 09 '19 at 02:20
  • @tadman i see now. noob @ sqlite3. I **thought** `sum()` summed up entire columns - and `insert` that summation into `Total`. This is a **category balances** tale (it sums up categories of amounts of things) – foo Apr 09 '19 at 02:47
  • `SUM()` will tally up all values in that column for all records matching the `WHERE` conditions, if any, or all records otherwise. I'm still not sure that this is the structure you want. Normally you sum from one table to another related, but different table, and do that with a `SUM(Amount) GROUP BY Type` to get aggregate sums per-type. – tadman Apr 09 '19 at 03:34
  • Oh, I think there's an error in how you compute average - you're calculating the total - current row value divided by the number of rows seen so far minus 1, not the number of total rows minus 1. That doesn't sound right. (Side note: Once sqlite 3.28 is out (Currently available for beta testing), you can calculate the average of all but the current row with `SELECT Amount, avg(Amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS AvgX FROM Table1 WHERE Type = :Type`) – Shawn Apr 09 '19 at 05:54
  • @Shawn that **is** a good observation! I have some work with this obviously. – foo Apr 09 '19 at 13:32

0 Answers0