将处理或函数应用于需要从另一个 sqlite 表中检索列的列 [英] Apply processing or function to column that requires retrieving column from another sqlite table

问题描述

假设我在 sqlite3 中有这个表 name_currency.

Suppose I have this table name_currency in sqlite3.

name,   currency,   price
AA,     SGD,        1
BB,     USD,        2
CC,     EUR,        3

我需要以这种方式处理价格.

I need to process the price in this manner.

if currency == "SGD", price = price*X 
if currency == "USD", price = price*Y
if currency == "EUR", price = price*Z

X、Y、Z 的值需要从另一个表currency_multiplier 中检索.这就是 currency_multiplier 的样子.

The values of X, Y, Z needs to be retrieved from another table currency_multiplier. This is how currency_multiplier looks like.

name,   currency,   multiplier
AA,     SGD,        2  #value of X 
BB,     USD,        3  #value of Y
CC,     EUR,        4  #value of Z

处理price列后的输出表如下所示；

The output table will look like this after processing price column;

name,   currency,   price
AA,     SGD,        2
BB,     USD,        6
CC,     EUR,        12

是否可以完全在 sql 中执行此操作?我想到的一种解决方案是从 name_currency 检索值，然后在 python 函数中进行处理.但是，我认为完全在 sql 中完成它更优雅.有关如何开始的任何提示?当处理变得过于复杂时，sqlite3 中的 SQL 是否支持某种形式的函数?

Is it possible to do this entirely in sql? One solution I have in mind is to retrieve the values from name_currency, then do the processing in a python function. However, I think it is more elegant to do it entirely in sql. Any hints on how to get started? Does SQL in sqlite3 support some form of function when processing becomes too complex?

推荐答案

您可以将 name_currency 表加入 currency_multiplier 表:

You may join the name_currency table to the currency_multiplier table:

SELECT
    n.name,
    n.currency,
    c.multiplier * n.price AS price
FROM name_currency n
INNER JOIN currency_multiplier c
    ON n.name = c.name;

请注意，这会导致从报告中删除出现在 name_currency 中但在 currency_multiplier 中没有映射的货币的风险.但是，在这种情况下，不清楚您将如何报告，因为乘数/外汇汇率未知.

Note that this runs the risk of dropping currencies from the report which appear in name_currency but have no mapping in currency_multiplier. But, in such a case, it is not clear how you would report anyway, because the multiplier/forex rate is not known.

这篇关于将处理或函数应用于需要从另一个 sqlite 表中检索列的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！