-1

I am using DB Browser for SQLite. Feel free to recommend another free GUI or software that uses SQL if SQLite is the problem.

I need to take the values from the percent_rank() function and use them for a later calculation. This could be done by exporting these values to a new table or appending them to the existing table (preferred). I don't think it is possible to reference these calculated columns. I cannot directly populate these columns because the UPDATE SET function will not work with the percent_rank() functions.

I have tried the following code based on this question. The code works to display the calculated values (Loan_def_rank) but the original data is unmodified.

SELECT a.entity_uuid,
    a.name,
    b.Loan_def_Rank

FROM data as a INNER JOIN
    (SELECT entity_uuid,
    percent_rank() OVER (PARTITION BY college_type ORDER BY loan_def_rate DESC) 
    AS Loan_def_Rank,
    FROM data GROUP BY entity_uuid) as b
    ON a.entity_uuid = b.entity_uuid;
GMB
  • 216,147
  • 25
  • 84
  • 135
JimK
  • 1
  • Why do you want to update the table with these values? You should derive them when you run your queries. Storing them just means (a) you'll have to be updating them _every time you make any other change_ or (b) they'll always potentially be out of date. – Aaron Bertrand Jul 16 '20 at 16:46
  • 1
    Why have you tagged this SQL Server if you are using SQL Lite? – Martin Smith Jul 16 '20 at 16:51
  • The SQL Server tag has been removed. – JimK Jul 16 '20 at 20:17
  • As I stated in the original question I need to reference them for multiplication to populate another column. If you have a solution to reference the percent rank value to multiply it, then set it into another column that would also work. – JimK Jul 16 '20 at 20:29

1 Answers1

0

You should not be storing this derived information. I would recommend using a view instead:

create view vdata as 
select 
    d.*,
    percent_rank() over (partition by college_type order by loan_def_rate desc) as loan_def_rank
from data d

The view contains all the columns of your original table, plus another one that contains the computed value. You can query the view just like you would query a table.

The upside is that you have an always up-to-date perspective at your data, without any maintenance cost (as opposed to actually storing the data, which requires you to manually keep it up to date).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • I successfully created a view but I still need to reference those values. I tried to create the score within the view creation and that did not work. I also tried to set the score after the view is created but that also didn't work. ```Update data score = vdata.loan_def_rate*10 ``` – JimK Jul 16 '20 at 20:39
  • Won't this has a massive impact on query performance, as it will have to recompute the percent_rank() on every query? – Kevin Fink Sep 17 '20 at 18:36