Hanukkah of Data是一系列以数据为主题的难题，您可以在其中解决难题，以使用虚构的数据集在以假期为主题的故事中移动。有关更多细节，请参见introductory post，但我脑海中的音调是“ Advent of Code遇见SQL Murder Mystery”。这篇文章介绍了我进入第七个难题的方法。
警告：这篇文章不可避免地包含破坏者。如果您想先自行执行难题，请关闭此浏览器选项卡并逃跑：）。
思维
事实证明，当艾米丽（Emily）抢劫诺亚（Noah）的盲人时，另一个客户以不同的颜色购买了同一商品：
好吧，我转过身，确定这个可爱的家伙拿着我买的东西。他说``我几乎完全相同！我们笑了，并结束了交换物品，因为他想要我得到的颜色。我们有片刻的眼睛相遇，我的心停了一秒钟。我要求他和我一起吃点食物，我们在一起度过了余下的一天。
因此，我们需要在艾米丽（Emily）的同一天找到订单，而该物品几乎与Emily购买的订单相同...
在大熊猫中解决这个问题，对我有些艰巨，所以我认为我会从SQL开始。浏览数据，我看到的颜色通常 在描述中显示在括号内。这似乎值得追求。
做（数据集）
艾米丽的命令
将问题分解为较小的零件，我认为我们想先获得艾米丽的订单。因此，我为其中设置了一个常见的表表达式：
with emily_orders as (
select
o.ordered,
p.sku,
p.desc
from
customers c
join orders o on c.customerid = o.customerid
join orders_items i on o.orderid = i.orderid
join products p on i.sku = p.sku
where
c.name = 'Emily Randolph'
)
艾米丽时代的订单
然后，我拉了与Emily的一位：
同一天放置的非emely订单
select
c.name,
c.address,
c.citystatezip,
c.phone,
o.ordered,
em.ordered as em_ordered,
p.sku,
em.sku as em_sku,
p.desc
from
customers c
join orders o on c.customerid = o.customerid
and date(o.ordered) in (
select
distinct date(ordered)
from
emily_orders
)
结果太多了，所以我微调了订单的时间，以寻找艾米丽订单5分钟内放置的非官方订单：
join emily_orders em on c.name != 'Emily Randolph'
and abs(
strftime('%s', o.ordered) - strftime('%s', em.ordered)
) <= 300
SKU前缀匹配
结果仍然太多了，所以我尝试按照与SKU前缀相匹配的订单过滤：
join emily_orders em on c.name != 'Emily Randolph'
join orders_items i on o.orderid = i.orderid
join products p on i.sku = p.sku
where
substr(p.sku, 1, 3) = substr(em.sku, 1, 3)
很好，我会过滤说明
我试图看看我是否可以比较描述，但我做不到。因此，我尝试通过将其添加到SELECT：
来匹配任何括号前的描述部分
case
when instr(p.desc, '(') = 0 then p.desc
else substr(p.desc, 1, instr(p.desc, '(') - 1)
end as stripped_desc,
case
when instr(em.desc, '(') = 0 then em.desc
else substr(em.desc, 1, instr(em.desc, '(') - 1)
end as em_stripped_desc
并在WHERE中匹配：
where
substr(p.sku, 1, 3) = substr(em.sku, 1, 3)
and stripped_desc = em_stripped_desc
这对我来说很尴尬，因为任何潜在的SQL Server或Postgres的本能都是无用的。但是它确实有效！
我认为有更好的方法可以通过扩展名和/或全文搜索支持。这感觉就像是解决缺乏模糊匹配或绳子分裂操作的一种非常丑陋的方式。
整体比零件的总和更丑
那是一个坎bump的旅程，我最终得到了这种怪兽：
with emily_orders as (
select
o.ordered,
p.sku,
p.desc
from
customers c
join orders o on c.customerid = o.customerid
join orders_items i on o.orderid = i.orderid
join products p on i.sku = p.sku
where
c.name = 'Emily Randolph'
)
select
c.name,
c.phone,
o.ordered,
em.ordered as em_ordered,
p.sku,
em.sku as em_sku,
p.desc,
case
when instr(p.desc, '(') = 0 then p.desc
else substr(p.desc, 1, instr(p.desc, '(') - 1)
end as stripped_desc,
em.desc,
case
when instr(em.desc, '(') = 0 then em.desc
else substr(em.desc, 1, instr(em.desc, '(') - 1)
end as em_stripped_desc
from
customers c
join orders o on c.customerid = o.customerid
and date(o.ordered) in (
select
distinct date(ordered)
from
emily_orders
) -- rough cut
join emily_orders em on c.name != 'Emily Randolph'
and abs(
strftime('%s', o.ordered) - strftime('%s', em.ordered)
) <= 300 -- fine tune
join orders_items i on o.orderid = i.orderid
join products p on i.sku = p.sku
where
substr(p.sku, 1, 3) = substr(em.sku, 1, 3)
and stripped_desc = em_stripped_desc
也是一个答案，所以我会接受。
做（熊猫）
因为SQL方法对我来说是如此严重，所以我试图重新考虑它，而不仅仅是将其“移植”到Pandas。起点是相同的 - 在同一天，在艾米丽订单的同一天找到一组不罚款的命令：
df['ordered_date'] = df.ordered.dt.date
emily = df[df.name == "Emily Randolph"]
not_emily = df[df.name != "Emily Randolph"]
merged = not_emily.merge(emily, on="ordered_date")
，但后来我试图提出一种更好的方法来指示“描述的颜色不同”。这很棘手！我对difflib进行了一些实验，这对于查看紧密的弦匹配很有趣。但是最后，我依靠一个小助手功能将描述分为一组低估的单词，并带有括号和其他标点符号：
import re
stemset = lambda x: {val.lower() for val in re.findall(r"\w+", x)}
我可以在transform()中使用，然后使用apply()找到每个艾米丽和不符合秩序之间的集合的对称差异：
merged["desc_diff"] = merged.transform({"desc_x": stemset, "desc_y": stemset}).apply(
lambda x: x.desc_x ^ x.desc_y, axis=1
)
由于颜色不是始终在项目描述中存在的，所以我认为我可以寻找描述单词之间的对称差异为1或2个单词的情况。将其与“彼此5分钟内的订单”结合在一起，这留下了：
merged[
merged.apply(
lambda x: abs((x.ordered_x - x.ordered_y).total_seconds()) <= 300
and len(x.desc_diff) in (1, 2),
axis=1,
)
]
棘手的颜色
我已经有了答案，但是有一件事na缩了我：我如何检查 color 差异，而不仅仅是1-2 word 区别？在这里，我不得不在拼图设计上摇晃生气而欣赏的拳头。因为虽然我可以在webcolors，Matplotlib，Crayola Apis等包装中找到颜色名称列表...此难题中的相关颜色不在Crayola/html/css颜色的核心集中。我发现最好的替代方法是拔出类似的颜色：
colors = set(
df.desc.transform(
lambda x: (match := re.search(r'\(([a-z]+)\)', x) ) and match.groups() or []
).explode().dropna().unique()
)
...然后检查以确保我的desc_diff列仅包含该集合中的单词。在这种情况下，最终没有必要或有用，但是我很好奇。
变得连锁
一些熊猫用户喜欢将其方法链接在一起，而不是留下中间变量的踪迹。我没有一致的偏好，但是双向工作感觉就像是好习惯。因此，如果我试图链接此PANDAS代码，它看起来像这样：
df[df.name != "Emily Randolph"].merge(
df[df.name == "Emily Randolph"], on="ordered_date"
).assign(
desc_diff=lambda x: x.transform({"desc_x": stemset, "desc_y": stemset}).apply(
lambda x: x.desc_x ^ x.desc_y, axis=1
)
).pipe(
lambda x: x[
x.apply(
lambda x: abs((x.ordered_x - x.ordered_y).total_seconds()) <= 300
and len(x.desc_diff) in (1, 2),
axis=1,
)
]
)
我希望当我浏览其他人的解决方案时，我会找到一个更干净的链式版本，因为这对我来说很粗糙。