需要的指导:前端用户动态选择字段的后端SQL逻辑 [英] Guidance needed: Backend SQL logic for dynamic selection of a field by users in frontend

问题描述

我有一个数据集，如下所示:

I have a dataset as follows:

Country,Commodity,Year,Type,Amount
US,Vegetable,2010,Harvested,2.44
US,Vegetable,2010,Yield,15.8
US,Vegetable,2010,Production,6.48
US,Vegetable,2011,Harvested,6
US,Vegetable,2011,Yield,18
US,Vegetable,2011,Production,3
Argentina,Vegetable,2010,Harvested,15.2
Argentina,Vegetable,2010,Yield,40.5
Argentina,Vegetable,2010,Production,2.66
Argentina,Vegetable,2011,Harvested,15.2
Argentina,Vegetable,2011,Yield,40.5
Argentina,Vegetable,2011,Production,2.66
Bhutan,Vegetable,2010,Harvested,7
Bhutan,Vegetable,2010,Yield,35
Bhutan,Vegetable,2010,Production,5
Bhutan,Vegetable,2011,Harvested,2
Bhutan,Vegetable,2011,Yield,6
Bhutan,Vegetable,2011,Production,3

给出:

1. 如果任何一个国家/地区的数据中包含n年，则所有其他国家也应具有相同的n年.例如:如果美国有2011年和2012年的数据，那么所有其他国家都将有2011年和2012年的数据.

条件:

1. 汇总仅在多国选择时发生.分组将按商品和年份进行.

例如:如果前端工具中的用户选择了美国和阿根廷，我们必须显示-

Eg: If a user in the frontend tool selects US and Argentina, we have to show -

衍生产量=(美国收获+阿根廷收获)/(美国产量+阿根廷产量)，即(2.44 + 15.2)/(6.48 + 2.66)，同样，对于三个国家来说，将是三个收获值的相加除以三个生产值的相加，依此类推.那必须在新行中填充.

The Amount for Derived Yield = (Harvested of US + Harvested of Argentina)/(Production of US + Production of Argentina), i.e., (2.44+15.2)/(6.48+2.66), similarly for three countries it will be addition of three harvested value divided by addition of three production value and so on. That has to be populated in a new row.

注意:前端用户可以选择任何国家/地区组合.在后端执行此操作而不是在前端动态执行操作的唯一目的是因为AWS QuickSight(我们的可视化工具)即使可以在选定的列过滤器上填充总和，但仍不支持对那些派生的求和字段进行计算.因此，必须预先填充所有国家组合的整个计算(非常幼稚的方法)，以使其在报告中可用.

Note: The users in the frontend can select any combination of countries. The sole purpose of doing it in the backend rather than dynamically doing it in the frontend is because AWS QuickSight (our visualisation tool), even though can populate sum on selected column filters but doesn't yet support calculation on those derived summed fields. Hence, the entire calculation of all combination of countries has to be pre-populated (very naive approach) in order to make it available in report.

我向所有SQL专家提出的两个问题是:

Two of my question to all SQL experts is:

• 如何填充按年份和商品分组的国家/地区的所有组合的行，以使其具有所有可能组合的数据.
• 鉴于我可以填充所有行组合，因此报表工具将如何理解要根据用户的国家/地区选择选择哪个派生行，因为该行的标记是US +阿根廷，该行是US +不丹，等

任何解决方案都非常受欢迎.

Any solution is extremely welcome.

SQL工具:Spark SQL或Athena SQL(在Presto上运行)或HiveQL. 不那么受欢迎:Oracle，PGSQL

SQL Tool preferred: Spark SQL or Athena SQL (runs on Presto) or HiveQL. Less preferred: Oracle, PGSQL

注释2 :即使我在另一个问题中也阐述了同样的问题，但发布此问题的唯一目的是因为我不想将天真的方法强加于试图解决该问题的人问题，因此在这里，我定义问题的方式比在解决方案中寻求帮助要清晰得多.而在另一个问题中，我给出了达到预期结果的方法.如果您想看到其他问题，请

Note 2: The sole purpose of posting this question, even though I've elaborated the same in another one is because I don't want to impose my naive approach on somebody trying to solve the problem, so here, I've defined the problem with more clarity than asking for help in solution. Whereas, in the other question I have given my approach for the expected result. In case if you want to see the other question, here it is.

推荐答案

您可以从以下内容开始:

you can start with something like this:

select * from
(
    select c.Country, y.Year
    from
    (select distinct Country from table) as c,
    (select distinct Year from table) as y
) as cy
left join table as t on t.Country = cy.Country and t.Year = cy.Year

这将为您提供所有包含国家/地区和年份组合以及主表中数据的所有行，因此您现在可以添加过滤器/分组

this will give you all rows with all combinations of Country/Year and optionally data from main table, so you can now add filter/grouping

这篇关于需要的指导:前端用户动态选择字段的后端SQL逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！