H&M 개인화 데이터셋으로
추천 그래프를 짓다

CREATE CONSTRAINT customer_id_idx FOR (n:Customer) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT postal_code_idx FOR (n:PostalCode) REQUIRE n.code IS UNIQUE;

LOAD CSV WITH HEADERS FROM "file:///customers.csv" as row
WITH row
CALL {
    WITH row
    MERGE (c:Customer {id:row.customer_id})
    SET c.age = row.age
    FOREACH( ignoreME in CASE WHEN row.fashion_news_frequency = 'Regularly'
        THEN [1] ELSE [] END |
        SET c:FN_REGULAR
    )
    FOREACH( ignoreME in CASE WHEN row.club_member_status = 'ACTIVE'
        THEN [1] ELSE [] END |
        SET c:CLUB_ACTIVE
    )
    FOREACH( ignoreME in CASE WHEN row.club_member_status = 'PRE-CREATE'
        THEN [1] ELSE [] END |
        SET c:CLUB_PRE_CREATE
    )
    FOREACH( ignoreME in CASE WHEN row.Active <> 'ACTIVE'
        THEN [1] ELSE [] END |
        SET c:INACTIVE
    )
    MERGE(p:PostalCode {code:row.postal_code})
    MERGE(c)-[:LIVES_IN]->(p)
} IN TRANSACTIONS OF 1000 ROWS

CREATE CONSTRAINT product_code_idx FOR (n:Product) REQUIRE n.code IS UNIQUE;
CREATE CONSTRAINT article_id_idx FOR (n:Article) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT product_type_id_idx FOR (n:ProductType) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT colour_group_idx FOR (n:ColorGroup) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT product_group_name_idx FOR (n:ProductGroup) REQUIRE n.name IS UNIQUE;
CREATE CONSTRAINT graphical_appearance_id_idx FOR (n:GraphicalAppearance) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT perceived_colour_id_idx FOR (n:PerceivedColor) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT department_id_idx FOR (n:Department) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT section_id_idx FOR (n:Section) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT garment_group_id_idx FOR (n:GarmentGroup) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT article_index_id_idx FOR (n:Index) REQUIRE n.id IS UNIQUE;
CREATE CONSTRAINT article_index_group_id_idx FOR (n:IndexGroup) REQUIRE n.id IS UNIQUE;

LOAD CSV WITH HEADERS FROM "file:///articles.csv" as row
WITH row
CALL {
    WITH row
    MERGE(a:Article {id:row.article_id})
    SET a.desc = row.detail_desc
    MERGE(p:Product {code:row.product_code})
    SET p.name = row.prod_name
    MERGE(a)-[:OF_PRODUCT]->(p)
    MERGE(pt:ProductType {id:row.product_type_no})
    SET pt.name = row.product_type_name
    MERGE(p)-[:HAS_TYPE]->(pt)
    WITH row, a, p
    MERGE(pg:ProductGroup {name:row.product_group_name})
    MERGE(p)-[:HAS_GROUP]->(pg)

    WITH row, a
    MERGE(g:GraphicalAppearance {id:row.graphical_appearance_no})
    SET g.name = row.graphical_appearance_name
    MERGE (a)-[:HAS_GRAPHICAL_APPEARANCE]->(g)
    WITH row, a
    MERGE (c:ColorGroup {id: row.colour_group_code})
    SET c.name = row.colour_group_name
    MERGE (a)-[:HAS_COLOR]->(c)
    WITH row, a
    MERGE (pc:PerceivedColor {id: row.perceived_colour_value_id})
    SET pc.name = row.perceived_colour_value_name
    MERGE (a)-[:HAS_PERCEIVED_COLOR]->(pc)
    MERGE (pcm:PerceivedColor {id: row.perceived_colour_master_id})
    SET pcm.name = row.perceived_colour_master_name
    MERGE (pc)-[:HAS_MASTER]->(pcm)

    WITH row, a
    MERGE (d:Department {id:row.department_no})
    SET d.name = row.department_name
    MERGE (a)-[:HAS_DEPARTMENT]->(d)
    WITH row, a
    MERGE (i:Index {id: row.index_code})
    SET i.name = row.index_name
    MERGE (a)-[:HAS_INDEX]->(i)
    MERGE (ig:IndexGroup {id: row.index_group_no})
    SET ig.name = row.index_group_name
    MERGE (i)-[:HAS_GROUP]->(ig)

    WITH row, a
    MERGE (s:Section {id: row.section_no})
    SET s.name = row.section_name
    MERGE (a)-[:HAS_SECTION]->(s)
    WITH row, a
    MERGE (gg:GarmentGroup {id: row.garment_group_no})
    SET gg.name = row.garment_group_name
    MERGE (a)-[:HAS_GARMENT_GROUP]->(gg)
} IN TRANSACTIONS OF 1000 ROWS

LOAD CSV WITH HEADERS FROM "file:///transactions_train.csv" as row
WITH row
CALL {
    WITH row
    MATCH (c:Customer {id:row.customer_id})
    MATCH (a:Article {id:row.article_id})
    WITH a, c, row
    CREATE (t:Transaction {date: row.t_dat, price: row.price,
        salesChannel: row.sales_channel_id})
    CREATE (t)-[:HAS_ARTICLE]->(a)
    WITH c, t
    CALL {
        -- 기존 체인이 있는 고객: LATEST를 옮겨 단다
        WITH c, t
        WITH c, t
        WHERE exists((c)-[:START_TRANSACTION]->())
           OR exists((c)-[:LATEST]->())
        MATCH (c)-[r:LATEST]->(lt)
        DELETE r
        CREATE (lt)-[:NEXT]->(t)
        CREATE (c)-[:LATEST]->(t)
        UNION
        -- 첫 거래인 고객: 열차의 머리를 만든다
        WITH c, t
        WITH c, t
        WHERE NOT ( exists((c)-[:START_TRANSACTION]->())
           OR exists((c)-[:LATEST]->()) )
        CREATE (c)-[:START_TRANSACTION]->(t)
        CREATE (c)-[:LATEST]->(t)
    }
} IN TRANSACTIONS OF 1000 ROWS

MATCH (c:Customer)
WITH c
CALL {
    WITH c
    MATCH (c)-[:START_TRANSACTION]->(s)
    MATCH (c)-[:LATEST]->(e)
    WITH c, s, e
    MATCH p=(s)-[:NEXT*]->(e)
    WITH c, nodes(p) as nodes
    UNWIND nodes as node
    WITH c, node, node.date as d
    WITH c, node, toInteger(substring(d, 0,4)) as year,
         substring(d, 5,2) as month
    WITH c, node,
        CASE WHEN month="12" THEN year
             WHEN month="01" OR month="02" THEN year-1
             ELSE year
        END as year,
        CASE WHEN month="12" OR month="01" OR month="02" THEN "WINTER"
             WHEN month="03" OR month="04" OR month="05" THEN "SPRING"
             WHEN month="06" OR month="07" OR month="08" THEN "SUMMER"
             WHEN month="09" OR month="10" OR month="11" THEN "FALL"
        END as season
    WITH c, node, season+'_'+year as relName
    WITH c, relName, head(collect(node)) as start
    WHERE relName is not null
    CALL apoc.create.relationship(c, relName, {}, start) YIELD rel
    WITH 1 as out
    return DISTINCT out
} IN TRANSACTIONS OF 1000 ROWS
WITH 1 as r
RETURN DISTINCT r

MATCH (c:Customer)-[:SUMMER_2019]->(start),
      (c)-[:FALL_2019]->()<-[:NEXT]-(end)
WITH c, start, end SKIP 100 LIMIT 1
MATCH p=(start)-[:NEXT*]->(end)
WITH nodes(p) as nodes, relationships(p) as rels
UNWIND nodes as node
MATCH p=(node)-[:HAS_ARTICLE]->(a)
RETURN a.desc as article