0

I have a table in MySQL that has a list of number for 505 rows

   1    2   3   4
------------------
A | 16   23  30  10
B  8    56  67   1

I am trying to divide each column by the last column and then deposit the results into a table of a similar format. I am a little lost on how to increment it. I have code that looks like this.

cursor.execute("select * from 'database'.'count'"
               " where name =%s;", name)
logs = cursor.fetchall()
cursor.execute("INSERT  INTO `database`.`averages`(`name`) "
                   "VALUES (%s) ON DUPLICATE KEY UPDATE "
                   "`1`=0, `2`=0, `3`=0, `4`=0, `5`=0, `6`=0,"
                   " `7`=0, `8`=0, `9`=0, `10`=0, `>10`=0,`-1`=0, `-2`=0, "
                   "`-3`=0, `-4`=0, `-5`=0, `-6`=0, `-7`=0, `-8`=0, "
                   "`-9`=0, `-10`=0, `<-10`=0;", name)
con.commit()
logs = [list (y) for y in logs]

from there I am drawing a blank on the best way to format the code, some sort of for loop comes to mind but is there a better way.

user3170242
  • 107
  • 11
  • 505 columns? Did I read that right? – Strawberry Aug 31 '16 at 00:03
  • whoops 505 rows 22 columns edited to reflect the mistake – user3170242 Aug 31 '16 at 00:23
  • Either way, see normalisation – Strawberry Aug 31 '16 at 01:45
  • its normalized, all values are unique, related, depend on the key and have a one to one relationship – user3170242 Aug 31 '16 at 03:30
  • Any time you have enumerated columns (above '2', say), alarm bells should start ringing. – Strawberry Aug 31 '16 at 07:15
  • Each column stores a value for total occurances of that number for a certain ID. I'm assuming your going to tell me to break each column into its own table, but because it's a calculated value from a python script and each value needs to be called again, it seems to me, it's more effort than its worth. I know that storing calculated values isn't always best practice but these values need to be queried later as well. – user3170242 Aug 31 '16 at 14:21
  • "I'm assuming your [sic] going to tell me to break each column into its own table" Certainly not! You should have one column storing the id, a second storing the value, and a third storing where that value occurs in the sequence. So, a total of 3 columns instead of 505. The PK would be (id,sequence) – Strawberry Aug 31 '16 at 15:20
  • Okay so under that structure the query to pull the information for the division problem would be something like SELECT * from database.averages group by ID. – user3170242 Aug 31 '16 at 15:59
  • Yes, except '*' would actually be some kind of aggregating function. If you provide a sample data set and corresponding desired result set, I can probably (I say brazenly) tell you what that function would be. – Strawberry Aug 31 '16 at 16:05
  • well in that format I guess you could write some sort of lag function couldn't you? `SELECT s.ID, s.streak, s.value, s.value/s.Last_price as Percent_diff FROM ( SELECT t.ID, t.streak, t.value, LAG(t.value) OVER (PARTITION BY t.ID ORDER BY t.ID, streak) Last_price FROM YourTable as t ) as s` – user3170242 Aug 31 '16 at 16:46
  • Except that this is a question about mysql. – Strawberry Aug 31 '16 at 16:56
  • ive seen ways you can simulate lag in sql http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – user3170242 Aug 31 '16 at 17:09
  • Yes. There are ways! – Strawberry Aug 31 '16 at 17:13

0 Answers0