ProductIndex全字段来源与逻辑.md 16.6 KB

Raw Blame History Permalink



yh_shops库product表


ProductIndex字段
yproduct表字段


product_id
product_id


product_skn
erp_product_id


shop_id
shop_id


product_name
product_name


cn_alphabet
cn_alphabet


sales_phrase
sales_phrase


brand_id
brand_id


max_sort_id
max_sort_id


middle_sort_id
middle_sort_id


small_sort_id
small_sort_id


series_id
series_id


gender
gender


gender_s
if(gender==1) gender_s='男' if(gender==2) gender_s='女' else gender_s='男,女'


style
style


isnew
if(to_days(now()) - to_days(from_unixtime(p.shelve_time))) <= 7) isnew='Y' else isnew='N'


islimited
if(is_limited=='Y') islimited='Y' else  islimited='N'


is_hot
is_hot


ishot
if(is_hot=='Y') ishot=1 else ishot=2


is_special
is_special


isspecial
if(is_special=='Y') isspecial=1 else isspecial=2


is_sales
is_sales


issales
if(is_sales=='Y') issales=1 else issales=2


is_advance
is_advance


isadvance
if(is_advance=='Y') isadvance=1 else isadvance=2


is_auditing
is_auditing


isauditing
if(is_auditing=='Y') isauditing=1 else isauditing=2


isrecommend
if(is_recommend=='Y') isrecommend=1 else isrecommend=2


is_promotion
is_promotion


attribute
attribute


seasons
seasons


seasons_s
if(seasons=='spring'


first_shelve_time
first_shelve_time


shelve_time
shelve_time


shelve_day
if(shelve_time==0) shelve_day=0 else  cast(from_unixtime(p.shelve_time) AS date)


edit_time
edit_time


is_down
is_down


isdown
if(is_down=='Y') isdown=1 else isdown=2


status
status


is_outlets
if(is_outlets=='Y') is_outlets=1 else is_outlets=2


folder_id
folder_id


sell_channels
sell_channels


age_level
age_level


app_type
app_type


-----
-----


###yh_shops库brand表

ProductIndex字段|  brand表字段 
-----|-----
brand_name_cn   |  brand_name_cn

brand_name_en   |  brand_name_en 
brand_keyword   |  brand_keyword

hot_keyword     |  hot_keyword

brand_name      |   brand_name

brand_domain    |  brand_domain

brand_alif      |   brand_alif 
-----|-----


yh_shops库product_sort表


ProductIndex字段
product_sort


max_sort
yh_shops库product_sort表sort_name  product_sort.max_sort_id =product.id  where p.max_sort_id = product_sort.id


max_sort_name
if(yh_shops库product_sort表status==0) max_sort_name=''else max_sort_name=     concat(p.max_sort_id,':',product_sort.sort_name,':',product_sort.order_by) where p.max_sort_id = product_sort.id


middle_sort
yh_shops库product_sort表sort_name  product_sort.middle_sort_id = product.id where p.max_sort_id = product_sort.id


middle_sort_name
if(yh_shops库product_sort表status==0) middle_sort_name=''else middle_sort_name=   concat(p.middle_sort_id,':',product_sort.sort_name,':',product_sort.order_by)where p.middle_sort_id = product_sort.id


small_sort
yh_shops库product_sort表sort_name  product_sort.small_sort_id =product.idwhere p.small_sort_id = product_sort.id


small_sort_name
if(yh_shops库product_sort表status==0) small_sort_name=''else small_sort_name= concat  (p.small_sort_id,':',product_sort.sort_name,':',product_sort.order_by)where p.small_sort_id = product_sort.id


is_instalment
yh_shops库product表is_instalment


country_id
yh_shops库product表country_id


yh_shops库product_price表

ProductIndex字段 | yh_shops库product_price表

条件：

`product.`id` = `product_price`.`product_id`

-----|-----|
special_price      |  special_price 
market_price       | market_price
vip_discount_type  |vip_discount_type
vip_price          | vip_price
vip1_price         |vip1_price 
vip2_price         |vip2_price 
vip3_price         |vip3_price 
sales_price        |sales_price
student_price      |student_price
is_student_price   |if(product_price.student_price IS NOT NULL)is_student_price='Y' elesis_student_price='N'
promotion_discount_int|  product_price.sales_price 除以 product_price.market_price  取到小数点第一位然后乘以10
promotion_discount    |product_price.sales_price 除以 product_price.market_price  取到小数点第三位
specialoffer          |product_price.sales_price 除以 product_price.market_price 取到小数点第一位 如果<0.5 specialoffer='Y' else specialoffer='N'
is_discount           | product_price.sales_price 除以 product_price.market_price 


yh_shops库product_keywords表


ProductIndex字段
yh_shops库product_keywords  product.id=product_keywords.product_id`


product_keyword
product_keyword


视图 product_colors 来源：yh_shops库goods表

###视图语句：

    SELECT
        `a`.`product_id` AS `product_id`,
        group_concat(`a`.`color_id` SEPARATOR ',') AS `color_ids`,
        group_concat(
            `a`.`color_name` SEPARATOR ','
        ) AS `color_names`
    FROM
        `goods` `a`
    GROUP BY
        `a`.`product_id`

ProductIndex字段  |yh_shops库product_colors表 条件： product.id=product_colors.product_id`
-----|-----|
color_ids|color_ids     把每个product对应的多个goods中的color_id拼起来
color_names|color_names 把每个product对应的多个goods中的color_name拼起来


视图product_standards 来源 yh_shops库product_standard_relation，standard，parameter_make表

###视图语句：

SELECT
    `a`.`product_id` AS `product_id`,
    group_concat(
        concat(
            `a`.`standard_id`,
            `a`.`parameter_make_id`
        ) SEPARATOR ','
    ) AS `standard_ids`,
    group_concat(
        concat(
            `a`.`standard_id`,
            '-',
            `b`.`standard_name`,
            ':',
            `a`.`parameter_make_id`,
            '-',
            `c`.`parameter_value`
        ) SEPARATOR ','
    ) AS `standard_names`,
    group_concat(
        `c`.`parameter_value` SEPARATOR ','
    ) AS `standard_only_names`
FROM
    (
        (
            `product_standard_relation` `a`
            LEFT JOIN `standard` `b` ON (
                (`a`.`standard_id` = `b`.`id`)
            )
        )
        JOIN `parameter_make` `c` ON (
            (
                `a`.`parameter_make_id` = `c`.`id`
            )
        )
    )
GROUP BY
    `a`.`product_id`

ProductIndex字段                  |yh_shops库product_standards表  条件：product.id=product_standards.product_id`
-----|-----|
standard_ids          | standard_ids   来自product_standard_relation表的standard_id和parameter_make_id字段
standard_names        |standard_names 来自product_standard_relation表的standard_id字段，standard表的standard_name字段，product_standard_relation表的parameter_make_id字段，parameter_make表的parameter_value字段
standard_only_names   |standard_only_names  来自parameter_make表的parameter_value字段


视图product_sizes  来源：yh_shops库 storage表，size表，storage表

###视图语句：

SELECT
    `t`.`product_id` AS `product_id`,
    group_concat(
        DISTINCT `t`.`goods_dimension_id` SEPARATOR ','
    ) AS `size_ids`,
    group_concat(
        DISTINCT `s`.`size_name` SEPARATOR ','
    ) AS `size_names`,
    sum(`t`.`sales_num`) AS `sales_num`,
    sum(`t`.`storage_num`) AS `storage_num`
FROM
    (
        (`storage` `t` JOIN `goods` `g`)
        JOIN `size` `s`
    )
WHERE
    (
        (`t`.`goods_id` = `g`.`id`)
        AND (`g`.`status` = 1)
        AND (
            `t`.`goods_dimension_id` = `s`.`id`
        )
    )
GROUP BY
    `t`.`product_id`

ProductIndex字段                  |yh_shops库product_sizes表  条件：product.id=product_sizes.product_id`
-----|-----|
sales_num               | sales_num   来自一个product对应的多个goods的sales_num之和
storage_num             |storage_num 来自一个product对应的多个goods的storage_num之和
is_soon_sold_out        |  if(storage_num<=2) is_soon_sold_out='Y' eles is_soon_sold_out='N'
is_sold_out             |  if(storage_num==0) is_sold_out='Y' eles is_sold_out='N'
sold_out                |   if(storage_num==0) sold_out='0' eles sold_out='1'
size_ids                | size_ids    来自一个product对应的多个goods通过storage的goods_dimension_id拼起来
size_names              |size_names  来自一个product对应的多个goods通过storage的goods_dimension_id查size表中的sizeName拼起来


视图product_styles  来源 yh_shops库 product_style_relation表

###视图语句：

(
    SELECT
        `a`.`product_id` AS `product_id`,
        group_concat(`a`.`style_id` SEPARATOR ',') AS `style_ids`
    FROM
        `product_style_relation` `a`
    GROUP BY
        `a`.`product_id`
)

ProductIndex字段                  |yh_shops库product_styles表  product.id=product_styles.product_id`
-----|-----|
style_ids|style_ids 将一个productId对应的product_style_relation表记录中的style_id拼起来


视图product_goods 来源： 视图product_good 来源 yh_shops库 goods表product_color表goods_cover_image视图(来自goods_images表)

###视图product_goods

(
    SELECT
        `t`.`product_id` AS `product_id`,
        concat(
            '[',
            group_concat(
                `t`.`goods_list` SEPARATOR ','
            ),
            ']'
        ) AS `goods_list`
    FROM
        `product_good` `t`
    GROUP BY
        `t`.`product_id`
)

###视图product_good

(
    SELECT
        `a`.`product_id` AS `product_id`,
        concat(
            '{"goods_id":"',
            `a`.`id`,
            '","color_name":"',
            `a`.`color_name`,
            '","cover_1":"',
            ifnull(`c`.`cover_1`, ''),
            '","cover_2":"',
            ifnull(`c`.`cover_2`, ''),
            '","color_id":"',
            `a`.`color_id`,
            '","status":"',
            `a`.`status`,
            '","color_code":"',
            ifnull(`b`.`color_code`, ''),
            '","color_value":"',
            '","images_url":"',
            ifnull(`a`.`color_image`, ''),
            '","product_skc":"',
            `a`.`product_skc`,
            '","is_default":"',
            `a`.`is_default`,
            '"}'
        ) AS `goods_list`,
        `a`.`is_default` AS `is_default`
    FROM
        (
            (
                `goods` `a`
                LEFT JOIN `product_color` `b` ON ((`a`.`color_id` = `b`.`id`))
            )
            LEFT JOIN `goods_cover_image` `c` ON ((`a`.`id` = `c`.`goods_id`))
        )
)

视图goods_cover_image

(
    SELECT
        `a`.`id` AS `id`,
        `a`.`goods_id` AS `goods_id`,
        (
            CASE `a`.`gender_cover`
            WHEN 1 THEN
                `a`.`image_url`
            END
        ) AS `cover_1`,
        (
            CASE `a`.`gender_cover`
            WHEN 2 THEN
                `a`.`image_url`
            END
        ) AS `cover_2`
    FROM
        `goods_images` `a`
    WHERE
        (`a`.`gender_cover` <> 0)
)

ProductIndex字段                  |yh_shops库product_goods表  条件： product.id=product_goods.product_id`
-----|-----|
goods_list     |goods_list
goods_list详情 |  详情
goods_id       |    goods表id字段
color_name     |  goods表color_name字段
cover_1        |     视图goods_cover_image的cover_1 来源： goods_images表中如果gender_cover=1，cover_1为goods_images表的image_url（gender_cover为0cover_1cover_2都为空）
cover_2        |     视图goods_cover_image的cover_2 来源：goods_images表中如果gender_cover=2，cover_2为goods_images表的image_url（gender_cover为0cover_1cover_2都为空）
color_id       |    goods表color_id字段
status         |         goods表status字段
color_code     |  product_color表color_code字段
color_value    | ""
images_url     |  goods表的color_image
product_skc    | goods表的product_skc
is_default     |  goods表的is_default

###视图product_default_image  来源：yh_shops库goods_images表 

    SELECT
        `t`.`product_id` AS `product_id`,
        `t`.`image_url` AS `default_images`
    FROM
        `goods_images` `t`
    WHERE
        (`t`.`is_default` = 'Y')
    GROUP BY
        `t`.`product_id`

ProductIndex字段  |yh_shops库product_default_image表  条件： product.id=product_default_image.product_id`
-----|-----|
default_images |default_images   goods_images中is_default=Y的记录的image_url字段值


product_activities视图 来源：product_activity视图 yh_shops库activity_product表

product_activities视图

    SELECT
        `t`.`product_skn` AS `product_skn`,
        concat(
            '[',
            group_concat(`t`.`activity` SEPARATOR ','),
            ']'
        ) AS `activities`
    FROM
        `product_activity` `t`
    GROUP BY
        `t`.`product_skn`

product_activity视图

(
    SELECT
        `ap`.`product_skn` AS `product_skn`,
        concat(
            '{"activity_id":',
            `ap`.`id`,
            ',"template_id":',
            `ap`.`template_id`,
            ',"order_by":',
            `ap`.`order_by`,
            ',"is_recommend":',
            `ap`.`is_recommend`,
            ',"status":',
            `ap`.`status`,
            '}'
        ) AS `activity`
    FROM
        `activity_product` `ap`
)

ProductIndex字段  |yh_shops库product_activities表 条件：  product.product_skn=product_activities.product_skn`
-----|-----|
activities          |activities
activities字段详情  | 详情
activity_id         |  id
template_id         |  template_id
order_by            |  order_by
is_recommend        | is_recommend
status              |  status


视图product_search_sort_weight  来源：product_search_sort视图和yh_shops库product_search表

视图product_search_sort_weight

SELECT
    `pss`.`product_skn` AS `product_skn`,
    `pss`.`sort_weight_id` AS `sort_weight_id`,
    `ps`.`int_value` AS `sort_weight`
FROM
    (
        `product_search_sort` `pss`
        JOIN `product_search` `ps`
    )
WHERE
    (
        `pss`.`sort_weight_id` = `ps`.`id`
    )

product_search_sort

SELECT
    `product_search`.`product_skn` AS `product_skn`,
    max(`product_search`.`id`) AS `sort_weight_id`
FROM
    `product_search`
WHERE
    (
        (
            `product_search`.`model_id` = 1
        )
        AND (
            `product_search`.`project_id` = 4
        )
    )
GROUP BY
    `product_search`.`product_skn`

ProductIndex字段                  |yh_shops-->product_search_sort_weight  条件： product.product_skn=product_search_sort_weight.product_skn`
-----|-----|
sort_weight|if(sort_weight==NULL)sort_weight=0  else  sort_weight  来源product_search的int_value字段


视图product_search_brand_weight 来源：product_search_brand视图和yh_shops库product_search表

视图product_search_brand_weight

SELECT
    `psb`.`product_skn` AS `product_skn`,
    `psb`.`brand_weight_id` AS `brand_weight_id`,
    `ps`.`int_value` AS `brand_weight`
FROM
    (
        `product_search_brand` `psb`
        JOIN `product_search` `ps`
    )
WHERE
    (
        `psb`.`brand_weight_id` = `ps`.`id`
    )

视图 视图product_search_brand

SELECT
    `product_search`.`product_skn` AS `product_skn`,
    max(`product_search`.`id`) AS `brand_weight_id`
FROM
    `product_search`
WHERE
    (
        (
            `product_search`.`model_id` = 1
        )
        AND (
            `product_search`.`project_id` = 1
        )
    )
GROUP BY
    `product_search`.`product_skn`

ProductIndex字段                  |yh_shops库product_search_brand_weight表 条件： product.product_skn=product_search_brand_weight.product_skn`
-----|-----|
brand_weight| if(brand_weight==NULL)brand_weight=0  else  brand_weight   来源：product_search 库int_value字段


yh_shops-->product_activities_link


ProductIndex字段
yh_shops库product_activities_link表  条件： product.product_skn=product_activities_link.product_skn`


yohood_id
activities_id


视图base_pin_ratio  来源：yh_shops库free_size_product表 和 视图base_pin_ratio_new_date

视图base_pin_ratio：

    SELECT
        `a`.`product_skn` AS `product_skn`,
        `a`.`base_pin_ratio` AS `base_pin_ratio`
    FROM
        (
            `free_size_product` `a`
            JOIN `base_pin_ratio_new_date` `b`
        )
    WHERE
        (
            `a`.`date_id` = `b`.`date_id`
        )
 
 视图base_pin_ratio_new_date
`
    SELECT
t.date_idASdate_id
    FROM
free_size_productt
    ORDER BY
t.date_id` DESC
    LIMIT 1

`
ProductIndex字段                  |yh_shops库base_pin_ratio表  条件：product.product_skn = base_pin_ratio.product_skn
-----|-----|
base_pin_ratio|base_pin_ratio  来源 free_size_product表base_pin_ratio字段


broken_code视图 来源 yh_shops库nonfree_size_product表 和视图broken_code_date

视图broken_code视图：
`
    SELECT
a.product_sknASproduct_skn,
a.breaking_rateASbreaking_rate
    FROM
        (
nonfree_size_producta
            JOINbroken_code_dateb
        )
    WHERE
        (
a.date_id=b.date_id`
        )


视图broken_code_date

(
    SELECT
        t.date_id AS date_id
    FROM
        nonfree_size_product t
    ORDER BY
        t.date_id DESC
    LIMIT 1
)
``


ProductIndex字段
yh_shops库broken_code表  product.product_skn=breaking_rate.breaking_rate`


breaking_rate
breaking_rate  来源 nonfree_size_product表breaking_rate字段


视图discount_product_skns 来源：yh_shops库discount_product表


ProductIndex字段
yh_shops-->discount_product_skn  product.product_skn=discount_product_skn.product_skn`


discount_score
score


pool_id
pool_id  一个product_skn对应discount_product中的记录的pool_id字段拼起来