sql - 连接两个表，但仅在多列匹配时才提取值
问题描述
我有下面的查询，它根据该周处理的音量分配站音量。站点大小基于全年查看 4 个数据点。这是因为站点在全年的流量波动，例如，它们可能在一个季度的流量小，但在另一个季度大。目标是为一个最适合全年的电台分配一个尺寸。下面的 sql 汇集了评估将应用的 station_size 大小所需的四个数据点。一年中可能发生的数量可能性有多种组合，这就是它们存储在单独表中的原因。我有一个名为“station_sizes”的单独表，它提供了基于从四个时间点获取的体积的站点大小的逻辑。
例如，如果一个站点在第 14 周处理小容量，在第 27 周处理小容量，在第 40 周处理标准容量，在第 47 周处理小容量，那么它们将是 ds 小型站点大小。station_size 表中有 1000 个场景。
我希望最终输出是查询输出 + station_size 的摘录。站大小将从 station_size 表中提取。
我需要知道如何让下面的查询引用车站大小表，以便它可以识别每个年份组是 wk_16= xyz.. 和 wk_26 =xyz.. 和 wk_39 = xyz.. 和 wk_43 =xyz 然后它会拉在尺寸逻辑中形成站尺寸表。
对于查询中的 ar 部分。我希望逻辑是，如果一个站至少对 AR 有“是”，它需要是标准站大小。我不确定我是否做对了。如果它比那个大但不小，我很好。
SELECT station,
del_date,
sum(volume)/7 as volume_ref,DATE_PART("week",del_date) as week_num,DATE_PART("year",del_date) as year,
CASE
when sum(volume)/7 between 0 and 20000 then 'ds x-small'
when sum(volume)/7 between 20000 and 36000 and max(ar) <> 'yes' then 'ds small'
when sum(volume)/7 between 36000 and 42000 then 'ds standard'
when sum(volume)/7 between 42000 and 72000 then 'ds large'
when sum(volume)/7 > 72000 then 'ds x-large'
when max(ar) = 'YES' then 'ds standard'
else 'ds small'
end as station_ref
FROM prophecy_na.na_topology_lrp
Right JOIN wbr_global.raw_station_extended_attribute
ON prophecy_na.na_topology_lrp.station = wbr_global.raw_station_extended_attribute.ds
where week_num in(16,26,39,43)
GROUP BY station, del_date;
[![在此处输入图像描述][1]][1] [![在此处输入图像描述][2]][2]
解决方案
看来您缺少一些说明，我稍后会讨论。您可能需要考虑为您的查询创建一个额外的查找表，例如 StationSize。
StationSizeID
Description
MinVolume
ButLessThan
然后您可以在不调整查询的情况下调整您的范围（以防其他人使用相同的阈值。您可以拥有诸如
StationSizeID Description MinVolume ButLessThan
1 ds x-small 0 20,000
2 ds small 20,000 36,000
3 ds standard 36,000 42,000
4 ds large 42,000 72,000
5 ds x-large 72,000 9,999,999
我们稍后会加入。
在您最后应用的情况下，您还可以对“max(ar)”进行测试，但也在您的 ds-small 测试条件下。我们不知道这个“ar”字段来自哪里，也不知道它的可能值的上下文。如果基于字符串并且您有一个值为“yes”的记录，那么它将取代任何“no”值，即使“no”是某个给定来源的最新值。此外，通过考虑案例/时间的成交量范围，并且任何给定周的成交量总和为 18,000，但它的 max(ar) 为“是”，它永远不会进入你的案例测试，将它推到“ds标准”级别。同样，如果您有 75k 卷和 max(ar) = "yes"，它也将保持 ds x-large 而不是 ds 标准。这是本意吗？如果预先测试“是”条件，所以如果曾经“是”，它总是被认为是一个标准站？如果是这样，这种情况需要改变。
现在，我的下一个级别需要澄清。您提到一年中有 4 周，但用户不知道检查给定周的内容/原因。如果给定电台根据音量活动增长/下降，您是否正在寻找音量活动？如果是这样，您是否尝试提供一些权重因子来升级/降级站点大小？根据您的示例：最初是小的，标准的，小的，小的总体上被认为是小的。如果场景是：小，小，标准，标准怎么办？它会因为它的增长而升级为标准尺寸的车站吗？
因此，不仅仅是人们向您提出疑问，了解您拥有什么，您正在寻找什么，只有适当的上下文才能真正为您提供您可能正在寻找的答案。我建议您编辑现有帖子，在该问题的详细信息中提供说明，而不是直接发表评论并保存。那么，您可以回复我以查看您发布的更改，我可以继续进行更多操作。
修订/反馈
好的，我想我正在从您的评论中关注您。您的第二个具有 1000 种组合的表是比较给定周的交易量的基础，并且您的表明确引用了 14、27、40 和 47 的 4 周。但您执行的查询实际上是每周的活动量根据您在 16、26、39、43 内查找日期周的查询，在不同的周内进行站点。因此，列标题实际上并不意味着 Wk14、Wk27、Wk40、Wk47。它们仅代表您想要查询的 4 周，例如您可以查询第 2、11、24、31 周？在这种情况下，无论第一周的状态是什么，都与您的站点大小表中“wk14”的匹配描述相关，对于 11 => Wk27、第 24 周 => Wk40 和最后一周 31 => Wk47 也是如此。这个站大小表有 1000'
但是，您没有我在第一个示例中模拟的显示低/高范围的站点大小表，对吗？如果没有也可以，只是想看看比赛场地上的所有棋子。
我是否更接近您想要完成的目标？
可能的解决方案 请注意......我不熟悉“dbeaver”数据库，但我敢打赌它是最常见的 sql 兼容。如果有什么不起作用，请告诉我，我们可以调整。
好的，我将尝试分段执行此操作，以便您了解所有内容如何联系在一起。让我们从每周分组查询开始
select
lrp.station,
DATE_PART("week", lrp.del_date) as WkNum,
sum( lrp.volume ) / 7 as WkAvg,
-- since the date part of the group by will always be the same
-- correlating to the 1st, 2nd, 3rd or 4th week, I now have this
-- extra column and just apply MAX(), otherwise SQL will NAG if
-- it is not part of group by and its not an aggregate function
max( case when DATE_PART("week", lrp.del_date) = 16 then 1
when DATE_PART("week", lrp.del_date) = 26 then 2
when DATE_PART("week", lrp.del_date) = 39 then 3
else 4 end ) as WeekOrder
from
prophecy_na.na_topology_lrp lrp
where
-- EXAMPLE, if you KNOW you only care about within a current year
-- this will help optimize by pre-qualifying the single year you
-- expect the week activity within
lrp.del_Date >= '2021-01-01'
AND lrp.del_date < '2022-01-01'
-- and NOW, only filter out for the 4 specific weeks you are about
AND DATE_PART("week", lrp.del_date) in (16,26,39,43)
group by
lrp.station,
DATE_PART("week", lrp.del_date)
现在，如果您想要滚动一年时间的几周，例如从 2020 年 10 月到 2021 年 8 月，只需将开始/结束日期过滤掉，这样您的“43”周将代表 2020 年和第 3 周将是 2021 年 1 月，您想要的几周的心理地图可能是（第 43 周 = 2020 年 10 月，第 2 周 = 2021 年 1 月，第 15 周 = 2021 年 4 月等），所以你的几周可能看起来像（43、2、15、21）。那么，您是否看到我认为您正试图覆盖的滚动周数？
如果是这样，上述查询可能会得出如下结果。
Station WkNum WkAvg WeekOrder
x 16 34142 1
x 26 36879 2
x 39 35387 3
x 43 37114 4
y 16 43872 1
y 26 41983 2
y 39 42218 3
y 43 39461 4
z 16 ...
因此，现在您有一个主记录列表，每个站和您希望每个查询的相应周，如果您进行了年度翻转，只需更改第 1-4 周的正确周顺序上下文的案例条件。
现在，我们可以针对您的案例运行 THIS 以获取范围以提取描述符。所以我将使用上面的整个查询作为“预查询”（pq 别名）并构建更多。但是现在，我将应用一个交叉表将 4 个可能的周拉入一行，这将更直接地匹配您的 Station_Size 表。
select
pq.Station,
max( case when pq.WeekOrder = 1
then VolRanges.StationSize else '' end ) as Wk1Size,
max( case when pq.WeekOrder = 2
then VolRanges.StationSize else '' end ) as Wk2Size,
max( case when pq.WeekOrder = 3
then VolRanges.StationSize else '' end ) as Wk3Size,
max( case when pq.WeekOrder = 4
then VolRanges.StationSize else '' end ) as Wk4Size,
-- just to tack on the "AR" status to extended properties
max( case when xa.station_id is null
then 'no' else 'yes' end ) as ARStatus
from
( entire first pre query above ) pq
JOIN
( select 'ds x-small' stationSize,
0 AtLeast,
20000 ButLessThan
UNION select 'ds small', 20000, 36000
UNION select 'ds standard', 36000, 42000
UNION select 'ds large', 42000, 72000
UNION select 'ds x-large', 72000, 9999999 ) VolRanges
on pq.WkAvg / 7 >= VolRanges.AtLeast
AND pq.WkAvg / 7 < VolRanges.ButLessThan
-- and also now getting the "ar" value from the extended properties
-- doing a LEFT-JOIN in case no such extended properties so the given
-- station is not left out of the query. So here, I am EXPLICITLY
-- pulling ONLY IF the ar = 'yes'
LEFT JOIN wbr_global.raw_station_extended_attribute xa
on pq.station_id = xa.ds
AND xa.ar = 'yes'
group by
pq.Station
所以现在，我们仍然每站和每周都有一排，但现在添加了每周的基础描述
Station Wk1Size Wk2Size Wk3Size Wk4Size ARStatus x ds small ds standard ds small ds small no y ds large ds standard ds large ds large no z ... DII1（来自属性的样本，假设聚合）是
现在，我们需要将每个单独的周与其对应的 4 部分周表联系起来，所以这是下一个嵌套，我会将它们绑定到有 4 个部分要匹配的工作站尺寸表。
select
perStation.*,
ss.station_size
from
( entire SECOND query above ) perStation
JOIN Station_Size ss
on perStation.Wk1Size = ss.wk_14
AND perStation.Wk2Size = ss.wk_27
AND perStation.Wk3Size = ss.wk_40
AND perStation.Wk4Size = ss.wk_47
因此，除了最终的“AR”考虑之外，上述所有内容都将得到。所以让我们看看“AR”=是的考虑。您有 5 种工作站尺寸类型。如果有任何东西高于标准，那就赢了。所以，你想要升级某人的唯一时间是如果 4wk 组合最终站大小 = "ds small" 并且 AR = "YES"。所以让我们补充一点，其他的，保持原样。即：如果站的 ar 状态为“否”并且它们的大小为“ds small”，那是它保持较小的唯一时间。
我只想更改以上内容以添加额外的 FinalStationSize 考虑因素。
select
perStation.*,
ss.station_size,
case when perStation.ARStatus = 'yes'
AND ss.Station_Size = 'ds small'
then 'ds standard'
else ss.Station_Size end FinalStationSize
您可能会遇到可能与 dbeaver 数据库不兼容的 SQL 错误，这就是为什么我尝试向您展示构建事物的各个步骤，然后将它们嵌套在一起以到达每个部分。如果有任何错误，请告诉我，我们可以尝试解决它们。
select
perStation.*,
ss.station_size,
case when perStation.ARStatus = 'yes'
AND ss.Station_Size = 'ds small'
then 'ds standard'
else ss.Station_Size end FinalStationSize
from
( select
pq.Station,
max( case when pq.WeekOrder = 1
then VolRanges.StationSize else '' end ) as Wk1Size,
max( case when pq.WeekOrder = 2
then VolRanges.StationSize else '' end ) as Wk2Size,
max( case when pq.WeekOrder = 3
then VolRanges.StationSize else '' end ) as Wk3Size,
max( case when pq.WeekOrder = 4
then VolRanges.StationSize else '' end ) as Wk4Size,
-- just to tack on the "AR" status to extended properties
max( case when xa.station_id is null
then 'no' else 'yes' end ) as ARStatus
from
( select
lrp.station,
DATE_PART("week", lrp.del_date) as WkNum,
sum( lrp.volume ) / 7 as WkAvg,
-- since the date part of the group by will always be the same
-- correlating to the 1st, 2nd, 3rd or 4th week, I now have this
-- extra column and just apply MAX(), otherwise SQL will NAG if
-- it is not part of group by and its not an aggregate function
max( case when DATE_PART("week", lrp.del_date) = 16 then 1
when DATE_PART("week", lrp.del_date) = 26 then 2
when DATE_PART("week", lrp.del_date) = 39 then 3
else 4 end ) as WeekOrder
from
prophecy_na.na_topology_lrp lrp
where
-- EXAMPLE, if you KNOW you only care about within a current year
-- this will help optimize by pre-qualifying the single year you
-- expect the week activity within
lrp.del_Date >= '2021-01-01'
AND lrp.del_date < '2022-01-01'
-- and NOW, only filter out for the 4 specific weeks you are about
AND DATE_PART("week", lrp.del_date) in (16,26,39,43)
group by
lrp.station,
DATE_PART("week", lrp.del_date) ) pq
JOIN
( select 'ds x-small' stationSize,
0 AtLeast,
20000 ButLessThan
UNION select 'ds small', 20000, 36000
UNION select 'ds standard', 36000, 42000
UNION select 'ds large', 42000, 72000
UNION select 'ds x-large', 72000, 9999999 ) VolRanges
on pq.WkAvg / 7 >= VolRanges.AtLeast
AND pq.WkAvg / 7 < VolRanges.ButLessThan
-- and also now getting the "ar" value from the extended properties
-- doing a LEFT-JOIN in case no such extended properties so the given
-- station is not left out of the query. So here, I am EXPLICITLY
-- pulling ONLY IF the ar = 'yes'
LEFT JOIN wbr_global.raw_station_extended_attribute xa
on pq.station_id = xa.ds
AND xa.ar = 'yes'
group by
pq.Station ) perStation
JOIN Station_Size ss
on perStation.Wk1Size = ss.wk_14
AND perStation.Wk2Size = ss.wk_27
AND perStation.Wk3Size = ss.wk_40
AND perStation.Wk4Size = ss.wk_47
推荐阅读
- ios - 如果 CNContactPickerViewController 中存在多个电话号码，如何显示
- javascript - 如何在反应js中将数据搜索参数保留在url中
- c# - 在公共类 C# & unity中添加文本
- angular - 在 Windows 下使用 chrome 在 Intellij 中调试 Angular 应用程序
- java - 创建新对象会覆盖与先前对象关联的 ObservableList
- spring-boot - rsyslog 是否与 Logback-SLF4J 兼容？
- swift - 删除tableview行和发布请求逻辑
- r - 如何在R中以字符形式构建条件滚动序列
- php - Laravel LdapRecord 数组
- java - 在 Spring Boot 中使用时 SonarQube API 日期+时区不起作用