pythondata 解法, 执行用时: 410 ms, 内存消耗: 67.6 MB, 提交时间: 2024-05-27 12:57:54

# 多个左连接
import pandas as pd

def price_at_given_date(products: pd.DataFrame) -> pd.DataFrame:
    # 1.选出id
    ids = products.loc[:, ['product_id']].drop_duplicates()

    # 2.先得到各id对应的最晚修改日期，再和products左连接，得到 id-price 表
    prod_price = products[products['change_date']<='2019-08-16'].groupby('product_id')['change_date'].max().reset_index()
    prod_price = prod_price.merge(products, on=['product_id', 'change_date'], how='left').rename(columns={'new_price':'price'})

    # 3.id 和 id-price 左连接，再fillna(10)
    ids = ids.merge(prod_price, on='product_id', how='left')

    return ids.fillna(10)[['product_id', 'price']]

mysql 解法, 执行用时: 319 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:12:07

# Write your MySQL query statement below
select
    product_id,
    price
from (
    select
        product_id,
        new_price as price,
        rank() over(partition by product_id order by change_date desc) as rk
    from Products
    where change_date <= '2019-08-16'
) t1 where rk = 1
union
select
    product_id,
    10 as price
from Products
group by product_id
having(min(change_date) > '2019-08-16');

mysql 解法, 执行用时: 308 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:11:26

# Write your MySQL query statement below
select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from products
) as p1 -- 所有的产品
left join (
    select product_id, new_price 
    from products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id;

mysql 解法, 执行用时: 1460 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 12:11:11

# Write your MySQL query statement below
SELECT
     distinct p1.product_id,
     coalesce((
         select 
            p2.new_price
        from
            Products p2
        where
            p2.product_id=p1.product_id AND p2.change_date <= '2019-08-16'
        order by
            p2.change_date DESC
        limit 1
     ),10) as price
from    
    Products p1;