copywriting_production/docs/数据库.md

Excel产品本月视图

CREATE OR REPLACE VIEW monthly_product_imports AS
SELECT 
    pi.*,
--    pi.product_name,
    competitor,
    EXISTS (
        SELECT 1
        FROM public.asinseed asinseed
        WHERE asinseed.asin = competitor
    ) AS asin_exists
FROM 
    public.productimport pi,
    LATERAL jsonb_array_elements_text(pi.product_data::jsonb->'competitor_list') AS competitor
WHERE 
    date_trunc('month', pi.created_at) = date_trunc('month', CURRENT_DATE);