I have the following tables
我有以下表格
Table Farm
+---------+--------+-------------------+-----------+------------+
| FARM_ID |Stock_ID| FARM_TITLE | Size | FARM_VALUE |
+---------+--------+-------------------+-----------+------------+
| 2 | 1 | AgriZone | M | 202 |
| 3 | 1 | Cow Mill | L | 11 |
| 4 | 2 | Beef Farm | H | 540 |
| 5 | 2 | CattleOne | M | 1080 |
| 6 | 2 | FarmOne | L | 455 |
| 7 | 3 | Perdue | H | 333 |
| 8 | 4 | Holstein | M | 825 |
| 10 | 1 | Dotterers | H | 98 |
+---------+--------+-------------------+-----------+------------+
Table Gate
+---------+---------+------------+
| GATE_ID | FARM_ID | FARM_VALUE |
+---------+---------+------------+
| 1 | 2 | 0 |
| 1 | 3 | 0 |
| 1 | 4 | 540 |
| 2 | 4 | 550 |
| 3 | 4 | 560 |
| 4 | 4 | 570 |
| 5 | 4 | 580 |
| 6 | 4 | 590 |
| 1 | 5 | 1080 |
| 2 | 5 | 1100 |
| 3 | 5 | 1120 |
| 4 | 5 | 1140 |
| 5 | 5 | 1160 |
| 6 | 5 | 1180 |
| 1 | 6 | 455 |
| 2 | 6 | 536 |
| 3 | 6 | 617 |
| 4 | 6 | 698 |
| 5 | 6 | 779 |
| 6 | 6 | 860 |
| 1 | 7 | 0 |
| 1 | 8 | 0 |
| 1 | 10 | 0 |
+---------+---------+------------+
Table Origin
+--------+----------+
| ORI_ID | ORI_NAME |
+--------+----------+
| 1 | US |
| 2 | CA |
| 3 | MX |
+--------+----------+
Table Stock
+--------+--------+-------------------+
|Stock_ID| ORI_ID | Stock_TITLE |
+--------+--------+-------------------+
| 1 | 1 | P1 |
| 2 | 2 | P3 |
| 3 | 3 | Q4 |
| 4 | 3 | B3 |
+--------+--------+-------------------+
Table Results
+-----------+---------+---------+------------+------------+
| RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE | Score% |
+-----------+---------+---------+------------+------------+
| 1 | 7 | 1 | 333 | 100 |
| 2 | 8 | 1 | 825 | 100 |
| 3 | 6 | 1 | 455 | 40 |
| 4 | 6 | 2 | 536 | 0 |
| 5 | 6 | 3 | 617 | 0 |
| 6 | 6 | 4 | 698 | 100 |
| 7 | 6 | 5 | 779 | 0 |
| 8 | 6 | 6 | 860 | 10 |
| 9 | 4 | 1 | 540 | 100 |
| 10 | 4 | 2 | 550 | 90 |
| 11 | 4 | 3 | 560 | 0 |
| 12 | 4 | 4 | 570 | 100 |
| 13 | 4 | 5 | 580 | 10 |
| 14 | 4 | 6 | 590 | 0 |
| 15 | 5 | 1 | 1080 | 0 |
| 16 | 5 | 2 | 1100 | 0 |
| 17 | 5 | 3 | 1120 | 0 |
| 18 | 5 | 4 | 1140 | 50 |
| 19 | 5 | 5 | 1160 | 0 |
| 20 | 5 | 6 | 1180 | 100 |
| 21 | 3 | 1 | 11 | 100 |
| 22 | 10 | 1 | 98 | 90 |
| 23 | 2 | 1 | 202 | 100 |
+-----------+---------+---------+------------+------------+
Annotated Result table: Same as above ^
带注释的结果表:与上面相同^
+-----------+---------+---------+------------+------------+
| RESULT_ID | FARM_ID | GATE_ID | FARM_VALUE | Score% |
+-----------+---------+---------+------------+------------+
+-----------+---------+---------+------------+------------+
| 1 | 7 | 1 | 333 | 100 | <--|H-Case {H}
+-----------+---------+---------+------------+------------+
+-----------+---------+---------+------------+------------+
| 2 | 8 | 1 | 825 | 100 | <--|M-Case {M}
+-----------+---------+---------+------------+------------+
+-----------+---------+---------+------------+------------+
| 3 | 6 | 1 | 455 | 40 |
| 4 | 6 | 2 | 536 | 0 |
| 5 | 6 | 3 | 617 | 0 |
| 6 | 6 | 4 | 698 | 100 | <--|L
| 7 | 6 | 5 | 779 | 0 | |
| 8 | 6 | 6 | 860 | 10 | |
+-----------+---------+---------+------------+------------+ |
| 9 | 4 | 1 | 540 | 100 | |
| 10 | 4 | 2 | 550 | 90 | |
| 11 | 4 | 3 | 560 | 0 | |
| 12 | 4 | 4 | 570 | 100 | <--+M-case {H,M,L}
| 13 | 4 | 5 | 580 | 10 | |
| 14 | 4 | 6 | 590 | 0 | |
+-----------+---------+---------+------------+------------+ |
| 15 | 5 | 1 | 1080 | 0 | |
| 16 | 5 | 2 | 1100 | 0 | |
| 17 | 5 | 3 | 1120 | 0 | |
| 18 | 5 | 4 | 1140 | 50 | <--|H
| 19 | 5 | 5 | 1160 | 0 |
| 20 | 5 | 6 | 1180 | 100 |
+-----------+---------+---------+------------+------------+
+-----------+---------+---------+------------+------------+
| 21 | 3 | 1 | 11 | 100 | <--|L
| 22 | 10 | 1 | 98 | 90 | <--+H-case {H,M,L}
| 23 | 2 | 1 | 202 | 100 | <--|M
+-----------+---------+---------+------------+------------+
Computations required:
- Type can have only three values at most: {H, M, L};
- When all values are present, they are graded as followed: H=70 M=20 L=10
-
ALL unique casese are
所有独特的案例都是
-
Case {H,M} : H=80 M=20
案例{H,M}:H = 80 M = 20
- Case {M,L} : M=60 L=40
- Case {H,L} : H=90 L=10
- Case {H} : H=100
- Case {M} : M=100
- Case {L} : L=100
- Case {H,M,L} : H=70 M=20 L=10
类型最多只能有三个值:{H,M,L};
当所有值都存在时,它们按如下分级:H = 70 M = 20 L = 10
案例{M,L}:M = 60 L = 40
案例{H,L}:H = 90 L = 10
案例{H}:H = 100
案例{M}:M = 100
案例{L}:L = 100
情况{H,M,L}:H = 70 M = 20 L = 10
Further Explanation
-
Only Stock with with atleast one
GATE, fully satisfied can get 100 points max- Example:
Q4has 3 sets of 6GATES; Only oneGATEset has to be satsified (have a score present). - The points present must be Multiplied against the particular case it pertains Example:
Q4has case {H,M,L} which means H=70; M=20; L=10 This would results in (70*100%)+(20*50%)+(10*100%)=90 (look back above at the Result table annotations)
示例:Q4有3组6 GATES;只有一个GATE集必须被满足(存在得分)。
存在的点必须与其所属的特定情况相乘例如:Q4具有情况{H,M,L},这意味着H = 70; M = 20; L = 10这将导致(70 * 100%)+(20 * 50%)+(10 * 100%)= 90(回顾上面的Result表注释)
2. - Example:
- Points should still be considered and accounted for even when a gate hasn't been fully satisfied. The gate with MAX points earned should be kept when no gates have been fully satisfied. (Will provide further explanation if not understood)
只有至少一个GATE的股票,完全满意可以获得100点最高例如:Q4有3套6 GATES;只有一个GATE集必须被满足(存在得分)。存在的点必须与其所属的特定情况相乘例如:Q4具有情况{H,M,L},这意味着H = 70; M = 20; L = 10这将导致(70 * 100%)+(20 * 50%)+(10 * 100%)= 90(回顾上面的Result表注释)2。
即使门没有完全满足,仍应考虑并计算积分。当没有完全满足门时,应保留获得MAX点的门。 (如果不理解将提供进一步的解释)
If we Perform a query to make sense of the tables and data it will look like below
如果我们执行查询以理解表和数据,它将如下所示
+---------+-----------+---------------+-----------+---------+-----------+---------+
| Origin | Stock | Farm Title | Farm Value| Gate | Size | Score |
+---------+-----------+---------------+-----------+---------+-----------+---------+
| US | P1 | Perdue | 333 | 1 | H | 100 |
| US | P3 | Holstein | 825 | 1 | M | 100 |
| CA | Q4 | FarmOne | 455 | 1 | L | 40 |
| CA | Q4 | FarmOne | 536 | 2 | L | 0 |
| CA | Q4 | FarmOne | 617 | 3 | L | 0 |
| CA | Q4 | FarmOne | 698 | 4 | L | 100 |
| CA | Q4 | FarmOne | 779 | 5 | L | 0 |
| CA | Q4 | FarmOne | 860 | 6 | L | 10 |
| CA | Q4 | Beef Farm | 540 | 1 | H | 0 |
| CA | Q4 | Beef Farm | 550 | 2 | H | 90 |
| CA | Q4 | Beef Farm | 560 | 3 | H | 0 |
| CA | Q4 | Beef Farm | 570 | 4 | H | 100 |
| CA | Q4 | Beef Farm | 580 | 5 | H | 10 |
| CA | Q4 | Beef Farm | 590 | 6 | H | 0 |
| CA | Q4 | CattleOne | 1080 | 1 | M | 0 |
| CA | Q4 | CattleOne | 1100 | 2 | M | 0 |
| CA | Q4 | CattleOne | 1120 | 3 | M | 0 |
| CA | Q4 | CattleOne | 1140 | 4 | M | 50 |
| CA | Q4 | CattleOne | 1160 | 5 | M | 100 |
| CA | Q4 | CattleOne | 1180 | 6 | M | 0 |
| MX | B3 | Cow Mill | 11 | 1 | L | 100 |
| MX | B3 | Dotterers | 98 | 1 | H | 90 |
| MX | B3 | AgriZone | 202 | 1 | M | 100 |
+---------+-----------+---------------+-----------+---------+-----------+---------+
Desire Results
+---------+-------------------+-------+
| Origin | Stock | score |
+---------+-------------------+-------+
| US | P1 | 100 |
| US | P3 | 100 |
| CA | Q4 | 90 |
| MX | B3 | 93 |
+---------+-------------------+-------+
Since origin has a stock which consists of 3 different farms and those farms have 6 gates each. As long as one gate-set (numerically matching gates) is scored to SOME value we can consider the entire STOCK found completely. This is the only way a stock can be considered 100.
由于原产地有一个由3个不同农场组成的库存,而这些农场每个有6个门。只要将一个门集(数字匹配门)评分为某个值,我们就可以认为完全找到了整个STOCK。这是股票被认为是100的唯一方式。
Moreover and to reiterate, STOCK Q4 has case: {H,M,L} and all of gate (4) was found to some degree. gate 4 has the score (100% * H) + (50% * M) + (100% * L) which equals (70*100%) + (20*50%) + (10*100%) = 90
此外,并重申,STOCK Q4有案例:{H,M,L}并且在某种程度上发现了所有门(4)。门4的得分(100%* H)+(50%* M)+(100%* L)等于(70 * 100%)+(20 * 50%)+(10 * 100%)= 90
Hence: (Taken from above)
因此:(取自上面)
| CA | Q4 | 90 |
QED
So what I need help with is creating the subquery/subselect to do make this computation work. I set up everything in the scenario above (along with a query in progress that I've been working with) in the SQL fiddle link below.
所以我需要帮助的是创建子查询/子选择来使这个计算工作。我在下面的SQL小提琴链接中设置了上面场景中的所有内容(以及我正在使用的正在进行的查询)。
Thanks greatly * community.
非常感谢*社区。
> The above problem in SqlFiddle can be found here <
>可以在这里找到SqlFiddle中的上述问题<
2 个解决方案
#1
4
Here is the query I've been working on. However, the results are slighly different from the ones you've posted in your question:
这是我一直在研究的查询。但是,结果与您在问题中发布的结果略有不同:
select o.origin_name, s.stock_title, sum(
case f.size
when 'H' then
case
when sizes = 'H,L,M' then 70
when sizes = 'H,M' then 80
when sizes = 'H,L' then 90
when sizes = 'H' then 100
else 0
end
when 'M' then
case
when sizes = 'H,L,M' then 20
when sizes = 'H,M' then 20
when sizes = 'L,M' then 60
when sizes = 'M' then 100
else 0
end
else
case
when sizes = 'H,L,M' then 10
when sizes = 'L,M' then 40
when sizes = 'H,L' then 10
when sizes = 'L' then 100
else 0
end
end * r.score / 100) FinalScore
from farm f
join (
select f.stock_id, group_concat(distinct f.size order by f.size) sizes
from farm f
join results r on f.farm_id = r.farm_id
group by f.stock_id
) stockSizes on f.stock_id = stockSizes.stock_id
join results r on f.farm_id = r.farm_id
join (
select f.stock_id, r.gate_id
from results r
join farm f on r.farm_id = f.farm_id
group by f.stock_id, r.gate_id
having sum(r.score = 0) = 0
) FullGates
on FullGates.stock_id = f.stock_id and FullGates.gate_id = r.gate_id
join stock s on s.stock_id = f.stock_id
join origin o on o.origin_id = s.origin_id
group by o.origin_id, s.stock_id
Result:
+-------------+-------------+------------+ | ORIGIN_NAME | STOCK_TITLE | FINALSCORE | +-------------+-------------+------------+ | US | P1 | 93 | | CA | P3 | 90 | | MX | Q4 | 100 | | MX | B3 | 100 | +-------------+-------------+------------+
Let me know if this did the trick.
让我知道这是否成功。
#2
1
I would take your original query to get the second last table and change the Select by adding use distinct (found here) and only select Origin, Stock and the calculation for the Score. For example if the score is an average of all of them it would be AVG(Score) where Score would be what you fetched in the original query. If you want to use only a small subset of the items that have the same Origin and Stock to calculate the Score I would use a subquery, with the where matching the Origin and Stick ids, in the select so you have:
我将采用您的原始查询来获取倒数第二个表并通过添加使用distinct(在此处找到)更改Select,并仅选择Origin,Stock和Score的计算。例如,如果分数是所有分数的平均值,那么AVG(分数)将是您在原始查询中获取的分数。如果你只想使用具有相同Origin和Stock的项目的一小部分来计算得分,我会在选择中使用子查询,其中匹配Origin和Stick ID,所以你有:
Select Origin,
Stock,
(select calculation(Score) from tables where tables.stock_id = .... tables.origin_id = .....)
From....
Hope this helps.
希望这可以帮助。
#1
4
Here is the query I've been working on. However, the results are slighly different from the ones you've posted in your question:
这是我一直在研究的查询。但是,结果与您在问题中发布的结果略有不同:
select o.origin_name, s.stock_title, sum(
case f.size
when 'H' then
case
when sizes = 'H,L,M' then 70
when sizes = 'H,M' then 80
when sizes = 'H,L' then 90
when sizes = 'H' then 100
else 0
end
when 'M' then
case
when sizes = 'H,L,M' then 20
when sizes = 'H,M' then 20
when sizes = 'L,M' then 60
when sizes = 'M' then 100
else 0
end
else
case
when sizes = 'H,L,M' then 10
when sizes = 'L,M' then 40
when sizes = 'H,L' then 10
when sizes = 'L' then 100
else 0
end
end * r.score / 100) FinalScore
from farm f
join (
select f.stock_id, group_concat(distinct f.size order by f.size) sizes
from farm f
join results r on f.farm_id = r.farm_id
group by f.stock_id
) stockSizes on f.stock_id = stockSizes.stock_id
join results r on f.farm_id = r.farm_id
join (
select f.stock_id, r.gate_id
from results r
join farm f on r.farm_id = f.farm_id
group by f.stock_id, r.gate_id
having sum(r.score = 0) = 0
) FullGates
on FullGates.stock_id = f.stock_id and FullGates.gate_id = r.gate_id
join stock s on s.stock_id = f.stock_id
join origin o on o.origin_id = s.origin_id
group by o.origin_id, s.stock_id
Result:
+-------------+-------------+------------+ | ORIGIN_NAME | STOCK_TITLE | FINALSCORE | +-------------+-------------+------------+ | US | P1 | 93 | | CA | P3 | 90 | | MX | Q4 | 100 | | MX | B3 | 100 | +-------------+-------------+------------+
Let me know if this did the trick.
让我知道这是否成功。
#2
1
I would take your original query to get the second last table and change the Select by adding use distinct (found here) and only select Origin, Stock and the calculation for the Score. For example if the score is an average of all of them it would be AVG(Score) where Score would be what you fetched in the original query. If you want to use only a small subset of the items that have the same Origin and Stock to calculate the Score I would use a subquery, with the where matching the Origin and Stick ids, in the select so you have:
我将采用您的原始查询来获取倒数第二个表并通过添加使用distinct(在此处找到)更改Select,并仅选择Origin,Stock和Score的计算。例如,如果分数是所有分数的平均值,那么AVG(分数)将是您在原始查询中获取的分数。如果你只想使用具有相同Origin和Stock的项目的一小部分来计算得分,我会在选择中使用子查询,其中匹配Origin和Stick ID,所以你有:
Select Origin,
Stock,
(select calculation(Score) from tables where tables.stock_id = .... tables.origin_id = .....)
From....
Hope this helps.
希望这可以帮助。