列向量和派生位向量的加权和 [英] Weighted sum of a column vector and a derived bit vector
问题描述
我们有一个报价表和两个买家的规模.尺寸为 s 的出价 p 表示买方愿意以价格 p 购买产品的 s 号.我们有一个包含四列的表格:
- 两个买家 pA 和 pB 的出价.
- 出价大小， sA 和 sB .
我们的工作是在表中添加一个新的最佳尺寸列( bS )，该列以最佳价格返回尺寸.如果两个买家的价格相同，则 bS 等于 sA + sB ，否则，我们需要采用提供更高价格的买家的出价大小. /p>
下面是带有所需输出的示例表.
该问题的简单解决方案:
SELECT pA, pB, sA, sB,
CASE
WHEN pA = pB THEN sA + sB
WHEN pA > pB THEN sA
ELSE sB
END AS bS
FROM t
现在让我们将问题推广到四个买家.一个标准的SQL解决方案是
WITH t_ext AS (
SELECT *, GREATEST(pA, pB, pC, pD) as bestPrice
FROM `t`
)
SELECT *, (sA * CAST(pA = bestPrice AS INT64) +
sB * CAST(pB = bestPrice AS INT64) +
sC * CAST(pC = bestPrice AS INT64) +
sD * CAST(pD = bestPrice AS INT64))
AS bS FROM t_ext
问题1)
是否存在一个简化的查询
- 使用函数SUM而不是手动添加四个项目
- 避免重复投射?
问题2)
Google BigQuery生态系统中是否有一种方法可以将该查询重用于具有列名称(例如，列号)的另一个表 priceA ， priceB 而不是 pA ， pB ?
顺便说一句.我写了一个博客文章关于这个问题的重点是Python和Q的解决方案，我想知道标准sql中最好的解决方案的样子.
以下是BigQuery标准SQL的通用名称，它不依赖于购买者数量以及价格和尺寸字段的命名.唯一的期望是所有价格都先上涨，然后是您示例中的所有各个尺寸.另外，我假设所有数字都是整数(例如上述示例)，但这可以进行调整以处理FLOAT
#standardSQL
WITH t_ext AS (
SELECT * EXCEPT(arr),
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS prices,
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET >= 4) AS sizes,
(SELECT MAX(CAST(val AS INT64)) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS bestPrice
FROM (
SELECT *, REGEXP_EXTRACT_ALL(TO_JSON_STRING(T), r':(\d+)') AS arr
FROM `project.dataset.table` t
)
)
SELECT * EXCEPT(prices, sizes),
(SELECT SUM(size)
FROM UNNEST(prices) price WITH OFFSET
JOIN UNNEST(sizes) size WITH OFFSET
USING(OFFSET)
WHERE price = bestPrice
) AS bS
FROM t_ext
您唯一需要在上方查询中更改的是购买者数量-在下方的表达式中(在下方的表达式中-4可以替换为ARRAY_LENGTH(arr) / 2
WHERE OFFSET < 4
WHERE OFFSET >= 4
WHERE OFFSET < 4
例如，对于以下虚拟数据(4个购买者)
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 pA, 2 pB, 3 pC, 4 pD, 1 sA, 1 sB, 1 sC, 5 sD UNION ALL
SELECT 1, 4, 2, 4, 1, 6, 1, 5 UNION ALL
SELECT 4, 4, 2, 1, 7, 1, 1, 1
), t_ext AS (
SELECT * EXCEPT(arr),
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS prices,
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET >= 4) AS sizes,
(SELECT MAX(CAST(val AS INT64)) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS bestPrice
FROM (
SELECT *, REGEXP_EXTRACT_ALL(TO_JSON_STRING(T), r':(\d+)') AS arr
FROM `project.dataset.table` t
)
)
SELECT * EXCEPT(prices, sizes),
(SELECT SUM(size)
FROM UNNEST(prices) price WITH OFFSET
JOIN UNNEST(sizes) size WITH OFFSET
USING(OFFSET)
WHERE price = bestPrice
) AS bS
FROM t_ext
结果是
Row pA pB pC pD sA sB sC sD bestPrice bS
1 1 2 3 4 1 1 1 5 4 5
2 1 4 2 4 1 6 1 5 4 11
3 4 4 2 1 7 1 1 1 4 8
We have a table of bid prices and sizes of two buyers. Bid price p with size s means that the buyer is open to buy s number of product at price p. We have a table of four columns:
- bid prices offered by the two buyers, pA and pB.
- bid sizes, sA and sB.
Our job is to add a new best size column (bS) to the table, that returns the size at the best price. If the two buyers have the same price then bS is equal to sA + sB, otherwise, we need to take the bid size of the buyer that offers the higher price.
An example table with the desired output is below.
A simple solution to the problem:
SELECT pA, pB, sA, sB,
CASE
WHEN pA = pB THEN sA + sB
WHEN pA > pB THEN sA
ELSE sB
END AS bS
FROM t
Now let us generalize the problem to four buyers. A standard SQL solution is
WITH t_ext AS (
SELECT *, GREATEST(pA, pB, pC, pD) as bestPrice
FROM `t`
)
SELECT *, (sA * CAST(pA = bestPrice AS INT64) +
sB * CAST(pB = bestPrice AS INT64) +
sC * CAST(pC = bestPrice AS INT64) +
sD * CAST(pD = bestPrice AS INT64))
AS bS FROM t_ext
Question 1)
Is there a simplified query that
- uses function SUM instead of adding four items manually
- avoids repeated casting?
Question 2)
Is there a way in Google BigQuery ecosystem to reuse this query for another table that has column name e.g. priceA, priceB instead of pA, pB?
Btw. I wrote a blog post about this problem that focuses on solutions in Python and Q and I am wondering how the best solution in standard sql looks like.
Below is for BigQuery Standard SQL an dis generic enough to not depend on number of buyers as well as naming for price and size fields. The only expectation is for all prices go first and then all respective sizes as it is in your example. Also i assume all numbers are integers (as in example in question) but this can be adjust to deal with FLOATs
#standardSQL
WITH t_ext AS (
SELECT * EXCEPT(arr),
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS prices,
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET >= 4) AS sizes,
(SELECT MAX(CAST(val AS INT64)) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS bestPrice
FROM (
SELECT *, REGEXP_EXTRACT_ALL(TO_JSON_STRING(T), r':(\d+)') AS arr
FROM `project.dataset.table` t
)
)
SELECT * EXCEPT(prices, sizes),
(SELECT SUM(size)
FROM UNNEST(prices) price WITH OFFSET
JOIN UNNEST(sizes) size WITH OFFSET
USING(OFFSET)
WHERE price = bestPrice
) AS bS
FROM t_ext
The only what you need to change in above query is number of buyers - in below expressions (in those below - 4 can be replaced with ARRAY_LENGTH(arr) / 2
WHERE OFFSET < 4
WHERE OFFSET >= 4
WHERE OFFSET < 4
For example, for below dummy data (4 buyers)
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 pA, 2 pB, 3 pC, 4 pD, 1 sA, 1 sB, 1 sC, 5 sD UNION ALL
SELECT 1, 4, 2, 4, 1, 6, 1, 5 UNION ALL
SELECT 4, 4, 2, 1, 7, 1, 1, 1
), t_ext AS (
SELECT * EXCEPT(arr),
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS prices,
ARRAY(SELECT CAST(val AS INT64) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET >= 4) AS sizes,
(SELECT MAX(CAST(val AS INT64)) FROM UNNEST(arr) val WITH OFFSET WHERE OFFSET < 4) AS bestPrice
FROM (
SELECT *, REGEXP_EXTRACT_ALL(TO_JSON_STRING(T), r':(\d+)') AS arr
FROM `project.dataset.table` t
)
)
SELECT * EXCEPT(prices, sizes),
(SELECT SUM(size)
FROM UNNEST(prices) price WITH OFFSET
JOIN UNNEST(sizes) size WITH OFFSET
USING(OFFSET)
WHERE price = bestPrice
) AS bS
FROM t_ext
result is
Row pA pB pC pD sA sB sC sD bestPrice bS
1 1 2 3 4 1 1 1 5 4 5
2 1 4 2 4 1 6 1 5 4 11
3 4 4 2 1 7 1 1 1 4 8
这篇关于列向量和派生位向量的加权和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
