In [1]:
%load_ext ngql
%ngql --address 127.0.0.1 --port 9669 --user root --password nebula
%ngql USE demo_basketballplayer
Connection Pool Created
Out[1]:
In [ ]:
%%ngql
CREATE SPACE `operator_biz` (partition_num = 1, replica_factor = 1, vid_type = FIXED_STRING(128));
In [2]:
%ngql USE operator_biz
Out[2]:
In [ ]:
%%ngql
CREATE TAG `profile` (`name` string NOT NULL) ttl_duration = 0, ttl_col = "", comment = "画像";
CREATE TAG `device` ( `IMEI` string NOT NULL, `model` string NOT NULL) ttl_duration = 0, ttl_col = "", comment = "设备";
CREATE TAG `device_model` ( `name` string NOT NULL, `price` float NOT NULL, `release_date` date NOT NULL) ttl_duration = 0, ttl_col = "", comment = "设备型号";
CREATE TAG `place` ( `geo_point` geography(point) NOT NULL, `name` string NULL, `city` string NOT NULL) ttl_duration = 0, ttl_col = "", comment = "地点";
CREATE TAG `place_category` (`name` string NOT NULL) ttl_duration = 0, ttl_col = "", comment = "地点类别";
CREATE TAG `product_category` ( `name` string NOT NULL) ttl_duration = 0, ttl_col = "", comment = "消费品类别";
CREATE TAG `subscriber` ( `msisdn` int64 NOT NULL, `name` string NOT NULL, `birth` date NOT NULL) ttl_duration = 0, ttl_col = "";
CREATE EDGE `was` (`time` datetime NOT NULL) ttl_duration = 0, ttl_col = "", comment = "是";
CREATE EDGE `is_categorized_as` () ttl_duration = 0, ttl_col = "", comment = "归类为";
CREATE EDGE `owned_device` ( `time` datetime NOT NULL) ttl_duration = 0, ttl_col = "", comment = "使用过设备";
CREATE EDGE `place_categorized_as` () ttl_duration = 0, ttl_col = "", comment = "地点归类";
CREATE EDGE `stays_in` ( `time` datetime NOT NULL) ttl_duration = 0, ttl_col = "", comment = "停留";
CREATE EDGE `with_model` () ttl_duration = 0, ttl_col = "", comment = "型号为";
CREATE EDGE `in_region_of` () ttl_duration = 0, ttl_col = "", comment = "在区域内";
In [ ]:
%%ngql
# Create Index
CREATE TAG INDEX IF NOT EXISTS any_shape_geo_index ON place(geo_point);
CREATE TAG INDEX IF NOT EXISTS subscriber_index ON subscriber(msisdn);
CREATE TAG INDEX IF NOT EXISTS subscriber_index ON subscriber(birth);
CREATE TAG INDEX IF NOT EXISTS profile_index ON `profile`(`name`(128));
In [3]:
%%ngql
INSERT VERTEX subscriber (msisdn, name, birth) VALUES
"sub-0":(13900000001, "Tom Smith", date("1998-03-17")),
"sub-1":(13900000002, "Emma Smith", date("1998-11-15")),
"sub-2":(13900000003, "Liam Johnson", date("2001-07-03")),
"sub-3":(13900000004, "Olivia Brown", date("1999-12-28")),
"sub-4":(13900000005, "Noah Davis", date("2000-03-10")),
"sub-5":(13900000006, "Ava Wilson", date("1997-09-05")),
"sub-6":(13900000007, "Sophia Taylor", date("1999-02-21")),
"sub-7":(13900000008, "Jackson Anderson", date("1998-06-12")),
"sub-8":(13900000009, "Isabella Martinez", date("1997-10-09")),
"sub-9":(13900000010, "Aiden Thompson", date("1996-04-17")),
"sub-10":(13900000011, "Mia Garcia", date("1995-08-25"));
INSERT VERTEX device_model (name, price, release_date) VALUES
"model-0":("iPhone 6", 999.0, date("2014-09-19")),
"model-1":("iPhone 7", 1999.0, date("2016-09-16")),
"model-2":("iPhone 8", 2999.0, date("2017-09-22")),
"model-3":("iPhone X", 3999.0, date("2017-11-03")),
"model-4":("iPhone 11", 4999.0, date("2019-09-20")),
"model-5":("RedMi 6", 999.0, date("2018-06-12")),
"model-6":("RedMi 7", 1299.0, date("2019-03-18")),
"model-7":("RedMi 8", 1499.0, date("2019-09-24")),
"model-8":("RedMi 9", 1699.0, date("2020-06-12"));
# device and device_model, with model from iPhone 6 to iPhone 11 and RedMi 6 to RedMi 9, imei is random generated
INSERT VERTEX device (IMEI, model) VALUES
"device-0":("123456789012345", "iPhone 6"),
"device-1":("123456789012346", "iPhone 7"),
"device-2":("123456789012347", "iPhone 8"),
"device-3":("123456789012348", "iPhone X"),
"device-4":("123456789012349", "iPhone 11"),
"device-5":("123456789012350", "RedMi 6"),
"device-6":("123456789012351", "RedMi 7"),
"device-7":("123456789012352", "RedMi 8"),
"device-8":("123456789012353", "RedMi 9");
INSERT EDGE with_model () VALUES
"device-0"->"model-0":(),
"device-1"->"model-1":(),
"device-2"->"model-2":(),
"device-3"->"model-3":(),
"device-4"->"model-4":(),
"device-5"->"model-5":(),
"device-6"->"model-6":(),
"device-7"->"model-7":(),
"device-8"->"model-8":();
# Those device-models current price lower than 2000 are categorized as "Cost-effective Product", above 2000 are categorized as "High-end Product"
INSERT VERTEX product_category (name) VALUES
"category-0":("Cost-effective Product"),
"category-1":("High-end Product");
INSERT EDGE is_categorized_as () VALUES
"model-0"->"category-0":(),
"model-1"->"category-0":(),
"model-2"->"category-0":(),
"model-3"->"category-1":(),
"model-4"->"category-1":(),
"model-5"->"category-0":(),
"model-6"->"category-0":(),
"model-7"->"category-0":(),
"model-8"->"category-0":();
# 高校与CBD办公地点
# Those places are categorized as "University" if it is a university, "CBD" if it is a CBD office building
INSERT VERTEX place_category (name) VALUES
"place-category-0":("University"),
"place-category-1":("CBD");
# 上海的高校、CBD
# 复旦大学 31.2974° N, 121.5036° E
# 上交大 31.0252° N, 121.4338° E
# 华东师范大学 31.2261° N, 121.4376° E
# 同济 31.2859° N, 121.5098° E
# 金茂大厦 31.2353° N, 121.5057° E
INSERT VERTEX place (geo_point, name, city) VALUES
"place-0":(ST_GeogFromText("POINT (121.5036 31.2974)"), "Fudan University", "Shanghai"),
"place-1":(ST_GeogFromText("POINT (121.4338 31.0252)"), "Shanghai Jiao Tong University", "Shanghai"),
"place-2":(ST_GeogFromText("POINT (121.4376 31.2261)"), "East China Normal University", "Shanghai"),
"place-3":(ST_GeogFromText("POINT (121.5098 31.2859)"), "Tongji University", "Shanghai"),
"place-4":(ST_GeogFromText("POINT (121.5057 31.2353)"), "Jinmao Building", "Shanghai");
# Mock places without name, that are 0.5km away from places above
INSERT VERTEX place (geo_point, name, city) VALUES
"place-10":(ST_GeogFromText("POINT (121.503517 31.298134)"), "", "Shanghai"),
"place-11":(ST_GeogFromText("POINT (121.503617 31.297431)"), "", "Shanghai"),
"place-12":(ST_GeogFromText("POINT (121.433813 31.025131)"), "", "Shanghai"),
"place-13":(ST_GeogFromText("POINT (121.433813 31.025331)"), "", "Shanghai"),
"place-14":(ST_GeogFromText("POINT (121.437613 31.226031)"), "", "Shanghai"),
"place-15":(ST_GeogFromText("POINT (121.437613 31.226231)"), "", "Shanghai"),
"place-16":(ST_GeogFromText("POINT (121.509813 31.285831)"), "", "Shanghai"),
"place-17":(ST_GeogFromText("POINT (121.509813 31.286031)"), "", "Shanghai"),
"place-18":(ST_GeogFromText("POINT (121.505123 31.235231)"), "", "Shanghai"),
"place-19":(ST_GeogFromText("POINT (121.506163 31.235431)"), "", "Shanghai");
INSERT EDGE place_categorized_as () VALUES
"place-0"->"place-category-0":(),
"place-1"->"place-category-0":(),
"place-2"->"place-category-0":(),
"place-3"->"place-category-0":(),
"place-4"->"place-category-1":();
INSERT VERTEX profile (name) VALUES
"profile-0":("Student"),
"profile-1":("C9"),
"profile-2":("Student Smart Phone"),
"profile-3":("Potential High-end Consumer");
INSERT EDGE owned_device (`time`) VALUES
"sub-0"->"device-0":(datetime("2023-06-01 12:00:00")),
"sub-1"->"device-1":(datetime("2023-06-11 13:01:30")),
"sub-2"->"device-2":(datetime("2023-06-21 14:03:00")),
"sub-3"->"device-3":(datetime("2023-06-02 15:04:30")),
"sub-4"->"device-4":(datetime("2023-06-12 16:06:00")),
"sub-5"->"device-5":(datetime("2023-06-22 17:07:30")),
"sub-6"->"device-6":(datetime("2023-06-03 18:09:00")),
"sub-7"->"device-7":(datetime("2023-06-13 19:10:30")),
"sub-8"->"device-8":(datetime("2023-06-23 20:12:00")),
"sub-9"->"device-0":(datetime("2023-06-04 21:13:30")),
"sub-10"->"device-1":(datetime("2023-06-14 22:15:00"));
# 年轻的人在学校,年长的人在CBD,时间是2023年6月份
INSERT EDGE stays_in (`time`) VALUES
"sub-0"->"place-10":(datetime("2023-06-01 12:00:00")),
"sub-1"->"place-11":(datetime("2023-06-11 13:01:30")),
"sub-2"->"place-12":(datetime("2023-06-21 14:03:00")),
"sub-3"->"place-13":(datetime("2023-06-02 15:04:30")),
"sub-4"->"place-14":(datetime("2023-06-12 16:06:00")),
"sub-5"->"place-15":(datetime("2023-06-22 17:07:30")),
"sub-6"->"place-15":(datetime("2023-06-03 18:09:00")),
"sub-7"->"place-16":(datetime("2023-06-13 19:10:30")),
"sub-8"->"place-17":(datetime("2023-06-23 20:12:00")),
"sub-9"->"place-18":(datetime("2023-06-04 21:13:30")),
"sub-9"->"place-17":(datetime("2022-06-04 21:13:30")),
"sub-10"->"place-19":(datetime("2023-06-14 22:15:00")),
"sub-10"->"place-10":(datetime("2022-06-14 21:11:00"));
Out[3]:
2. 画像、关系生成¶
- 当前价格低于 2000 的手机为高性价比手机
- 小于30岁的 subscriber ,如果在学校,那么他们是学生;
- 在复旦大学、上海交通大学的学生是 C9高校画像;
- 使用高性价比手机的学生,那么他们是学生智能画像;
- 拥有学生智能画像与 C9高校画像,那么他们是潜在高消费用户画像
2.1 新标签:当前价格低于 2000 的手机为高性价比手机¶
In [4]:
%%ngql
MATCH (sub:subscriber{name: "Tom Smith"})-[:owned_device]->(:device)-[:with_model]->(model:device_model)
WHERE model.device_model.price < 2000
RETURN id(sub) AS id, properties(sub).name AS name,
model.device_model.name AS model_name,
model.device_model.price AS price_now,
(model.device_model.price < 2000) AS is_cost_effective_product;
Out[4]:
id | name | model_name | price_now | is_cost_effective_product | |
---|---|---|---|---|---|
0 | sub-0 | Tom Smith | iPhone 6 | 999.0 | True |
In [5]:
%%ngql
# Those device-models current price lower than 2000 are categorized as "Cost-effective Product", above 2000 are categorized as "High-end Product"
# INSERT VERTEX product_category (name) VALUES
# "category-0":("Cost-effective Product"),
# "category-1":("High-end Product");
INSERT EDGE is_categorized_as () VALUES
"model-0"->"category-0":(),
"model-1"->"category-0":(),
"model-2"->"category-0":(),
"model-3"->"category-1":(),
"model-4"->"category-1":(),
"model-5"->"category-0":(),
"model-6"->"category-0":(),
"model-7"->"category-0":(),
"model-8"->"category-0":();
Out[5]:
In [6]:
%%ngql
MATCH p=(sub:subscriber{name: "Tom Smith"})-[:owned_device]->(:device)-[:with_model]->(model:device_model)
RETURN p
Out[6]:
p | |
---|---|
0 | ("sub-0" :subscriber{msisdn: 13900000001, name... |
In [7]:
%ng_draw
nebulagraph_draw.html
Out[7]:
In [9]:
!mv nebulagraph_draw.html ng_draw_2.1.html
2.2 画像:小于30岁的 subscriber ,如果在(复旦)大学附近,那么他们是学生;¶
MATCH (sub:subscriber)-[:stays_in]->(place:place)
WHERE (
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5036 31.2974)")
) < 500
OR
place.place.name == "Fudan University")
AND
sub.subscriber.birth > date("1998-12-31")
RETURN id(sub) AS id, sub.subscriber.name AS name, sub.subscriber.birth AS birth,
"Student" AS profile
In [10]:
import folium
import math
# INSERT VERTEX place (geo_point, name, city) VALUES
# "place-0":(ST_GeogFromText("POINT (121.5036 31.2974)"), "Fudan University", "Shanghai"),
# "place-1":(ST_GeogFromText("POINT (121.4338 31.0252)"), "Shanghai Jiao Tong University", "Shanghai"),
# "place-2":(ST_GeogFromText("POINT (121.4376 31.2261)"), "East China Normal University", "Shanghai"),
# "place-3":(ST_GeogFromText("POINT (121.5098 31.2859)"), "Tongji University", "Shanghai"),
# "place-4":(ST_GeogFromText("POINT (121.5057 31.2353)"), "Jinmao Building", "Shanghai");
# INSERT VERTEX place (geo_point, name, city) VALUES
# "place-10":(ST_GeogFromText("POINT (121.503517 31.298134)"), "", "Shanghai"),
# "place-11":(ST_GeogFromText("POINT (121.503617 31.297431)"), "", "Shanghai"),
# "place-12":(ST_GeogFromText("POINT (121.433813 31.025131)"), "", "Shanghai"),
# "place-13":(ST_GeogFromText("POINT (121.433813 31.025331)"), "", "Shanghai"),
# "place-14":(ST_GeogFromText("POINT (121.437613 31.226031)"), "", "Shanghai"),
# "place-15":(ST_GeogFromText("POINT (121.437613 31.226231)"), "", "Shanghai"),
# "place-16":(ST_GeogFromText("POINT (121.509813 31.285831)"), "", "Shanghai"),
# "place-17":(ST_GeogFromText("POINT (121.509813 31.286031)"), "", "Shanghai"),
# "place-18":(ST_GeogFromText("POINT (121.505123 31.235231)"), "", "Shanghai"),
# "place-19":(ST_GeogFromText("POINT (121.506163 31.235431)"), "", "Shanghai");
points_subscriber = [
(31.298134, 121.503517, "sub-0"),
(31.297431, 121.503617, "sub-1"),
(31.025131, 121.433813, "sub-2"),
(31.025331, 121.433813, "sub-3"),
(31.226031, 121.437613, "sub-4"),
(31.226231, 121.437613, "sub-5"),
(31.226231, 121.437613, "sub-6"),
(31.285831, 121.509813, "sub-7"),
(31.286031, 121.509813, "sub-8"),
(31.235231, 121.505123, "sub-9"),
(31.235431, 121.506163, "sub-10"),
]
points_place = [
# Fudan University
(31.2974, 121.5036, "Fudan University"),
# Shanghai Jiao Tong University
(31.0252, 121.4338, "Shanghai Jiao Tong University"),
# East China Normal University
(31.2261, 121.4376, "East China Normal University"),
# Tongji University
(31.2859, 121.5098, "Tongji University"),
# JinMao Building
(31.2353, 121.5057, "Jinmao Building"),
]
# JinMao Building
shagnhai_cbd = [31.2353, 121.5057]
# draw circle of points_place places in folium map
def draw_circle(map_graph, points_place, radius):
for point in points_place:
folium.Circle(location=point[:2], radius=int(radius*1000), color='blue', fill=True, fill_color='#3186cc').add_to(map_graph)
# draw points of points_subscriber in folium map
def draw_points(map_graph, points_subscriber, color='green', icon='user'):
for point in points_subscriber:
# plot the points with icon of user
lat, lon = point[0], point[1]
vertex_id = point[2]
folium.Marker(location=[lat, lon], popup=vertex_id, icon=folium.Icon(
color=color,
icon=icon,
)).add_to(map_graph)
# draw connections between points_subscriber and points_place if the distance is less than radius
def draw_connections(map_graph, points_subscriber, points_place):
for point_subscriber in points_subscriber:
for point_place in points_place:
lat_subscriber, lon_subscriber = point_subscriber[0], point_subscriber[1]
lat_place, lon_place = point_place[0], point_place[1]
distance = math.sqrt((lat_subscriber-lat_place)**2 + (lon_subscriber-lon_place)**2)
if distance < 0.001:
folium.PolyLine(locations=[point_subscriber[:2], point_place[:2]], color='red').add_to(map_graph)
# Create a folium map centered at CBD
map_graph = folium.Map(location=shagnhai_cbd, zoom_start=13)
draw_circle(map_graph, points_place, 1)
draw_points(map_graph, points_subscriber)
draw_points(map_graph, points_place, color='blue', icon='home')
draw_connections(map_graph, points_subscriber, points_place)
# Save the map to an HTML file
#map_graph.save("nodes_with_circles.html")
from IPython.display import display
display(map_graph)
Make this Notebook Trusted to load map: File -> Trust Notebook
In [11]:
%%ngql
MATCH (sub:subscriber)-[:stays_in]->(place:place)
WHERE (
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5036 31.2974)")
) < 1000
OR
place.place.name == "Fudan University")
AND
sub.subscriber.birth > date("1994-12-31")
RETURN id(sub) AS id, sub.subscriber.name AS name, sub.subscriber.birth AS birth,
"Student" AS profile
Out[11]:
id | name | birth | profile | |
---|---|---|---|---|
0 | sub-0 | Tom Smith | 1998-03-17 | Student |
1 | sub-1 | Emma Smith | 1998-11-15 | Student |
2 | sub-10 | Mia Garcia | 1995-08-25 | Student |
In [12]:
%%ngql
MATCH p=(sub:subscriber)-[:stays_in]->(place:place)
WHERE (
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5036 31.2974)")
) < 1000
OR
place.place.name == "Fudan University")
AND
sub.subscriber.birth > date("1996-12-31")
RETURN p
Out[12]:
p | |
---|---|
0 | ("sub-0" :subscriber{msisdn: 13900000001, name... |
1 | ("sub-1" :subscriber{msisdn: 13900000002, name... |
In [13]:
%ng_draw
nebulagraph_draw.html
Out[13]:
In [14]:
!mv nebulagraph_draw.html ng_draw_2.2.html
In [15]:
%%ngql
# 增加边:Student 画像
INSERT EDGE was (`time`) VALUES
"sub-0"->"profile-0":(datetime("2023-06-01 12:00:00")),
"sub-1"->"profile-0":(datetime("2023-06-11 13:01:30")),
"sub-2"->"profile-0":(datetime("2023-06-21 14:03:00")),
"sub-3"->"profile-0":(datetime("2023-06-02 15:04:30")),
"sub-4"->"profile-0":(datetime("2023-06-12 16:06:00")),
"sub-5"->"profile-0":(datetime("2023-06-22 17:07:30")),
"sub-6"->"profile-0":(datetime("2023-06-03 18:09:00")),
"sub-7"->"profile-0":(datetime("2023-06-13 19:10:30")),
"sub-8"->"profile-0":(datetime("2023-06-23 20:12:00")),
"sub-9"->"profile-0":(datetime("2023-06-04 21:13:30")),
"sub-10"->"profile-0":(datetime("2022-06-14 22:15:00"));
Out[15]:
In [16]:
%%ngql
MATCH p=(:`profile`{name:"Student"})<-[:was]-(sub:subscriber)-[:stays_in]->(place:place)
RETURN p
Out[16]:
p | |
---|---|
0 | ("profile-0" :profile{name: "Student"})<-[:was... |
1 | ("profile-0" :profile{name: "Student"})<-[:was... |
2 | ("profile-0" :profile{name: "Student"})<-[:was... |
3 | ("profile-0" :profile{name: "Student"})<-[:was... |
4 | ("profile-0" :profile{name: "Student"})<-[:was... |
5 | ("profile-0" :profile{name: "Student"})<-[:was... |
6 | ("profile-0" :profile{name: "Student"})<-[:was... |
7 | ("profile-0" :profile{name: "Student"})<-[:was... |
8 | ("profile-0" :profile{name: "Student"})<-[:was... |
9 | ("profile-0" :profile{name: "Student"})<-[:was... |
10 | ("profile-0" :profile{name: "Student"})<-[:was... |
11 | ("profile-0" :profile{name: "Student"})<-[:was... |
12 | ("profile-0" :profile{name: "Student"})<-[:was... |
13 | ("profile-0" :profile{name: "Student"})<-[:was... |
In [17]:
%ng_draw
nebulagraph_draw.html
Out[17]:
In [18]:
!mv nebulagraph_draw.html ng_draw_2.2_b.html
2.3 图上增加画像:在复旦大学、上海交通大学的学生是 C9高校画像¶
Query based on GEO distance
MATCH (place:place)<-[:stays_in]-(sub:subscriber)-[:was]->(:`profile`{name:"Student"})
WHERE (
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5036 31.2974)")
) < 1000
OR
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.4338 31.0252)")
) < 1000
OR
place.place.name IN ["Fudan University", "Shanghai Jiao Tong University"])
AND
sub.subscriber.birth > date("1996-12-31")
RETURN id(sub) AS id, sub.subscriber.name AS name, sub.subscriber.birth AS birth,
"C9" AS profile
In [19]:
%%ngql
MATCH (place:place)<-[:stays_in]-(sub:subscriber)-[:was]->(:`profile`{name:"Student"})
WHERE (
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5036 31.2974)")
) < 1000
OR
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.4338 31.0252)")
) < 1000
OR
place.place.name IN ["Fudan University", "Shanghai Jiao Tong University"])
AND
sub.subscriber.birth > date("1996-12-31")
RETURN id(sub) AS id, sub.subscriber.name AS name, sub.subscriber.birth AS birth,
"C9" + ", " + "Student" AS profile
Out[19]:
id | name | birth | profile | |
---|---|---|---|---|
0 | sub-0 | Tom Smith | 1998-03-17 | C9, Student |
1 | sub-1 | Emma Smith | 1998-11-15 | C9, Student |
2 | sub-2 | Liam Johnson | 2001-07-03 | C9, Student |
3 | sub-3 | Olivia Brown | 1999-12-28 | C9, Student |
2.3.1 加边:常驻地址的区域归属¶
Or, we could add in_region_of relationship between place and region, and query based on region.
In [20]:
%%ngql
# 增加边:标记地位位置在区域内
INSERT EDGE in_region_of () VALUES
"place-10"->"place-0":(),
"place-11"->"place-0":(),
"place-12"->"place-1":(),
"place-13"->"place-1":(),
"place-14"->"place-2":(),
"place-15"->"place-2":(),
"place-15"->"place-3":(),
"place-16"->"place-3":(),
"place-17"->"place-4":(),
"place-18"->"place-4":(),
"place-19"->"place-4":();
Out[20]:
通过这个新加的边可以获得更多解释性
Then we could query with in_region_of
relationship
In [21]:
%%ngql
MATCH (region:place)<-[:in_region_of]-(:place)<-[:stays_in]-(sub:subscriber)-[:was]->(:`profile`{name:"Student"})
WHERE region.place.name IN ["Fudan University", "Shanghai Jiao Tong University"]
AND
sub.subscriber.birth > date("1994-12-31")
RETURN id(sub) AS id, sub.subscriber.name AS name, sub.subscriber.birth AS birth,
"C9" + ", " + "Student" AS profile
Out[21]:
id | name | birth | profile | |
---|---|---|---|---|
0 | sub-10 | Mia Garcia | 1995-08-25 | C9, Student |
1 | sub-0 | Tom Smith | 1998-03-17 | C9, Student |
2 | sub-1 | Emma Smith | 1998-11-15 | C9, Student |
3 | sub-3 | Olivia Brown | 1999-12-28 | C9, Student |
4 | sub-2 | Liam Johnson | 2001-07-03 | C9, Student |
2.3.2 增加 C9 画像¶
In [22]:
%%ngql
# 增加边:C9 画像
INSERT EDGE was (`time`) VALUES
"sub-0"->"profile-1":(datetime("2023-06-11 12:00:00")),
"sub-1"->"profile-1":(datetime("2023-06-21 13:01:30")),
"sub-2"->"profile-1":(datetime("2023-06-30 14:03:00")),
"sub-3"->"profile-1":(datetime("2023-06-12 15:04:30")),
"sub-10"->"profile-1":(datetime("2022-06-12 11:04:30"));
Out[22]:
In [23]:
%%ngql
MATCH p=(region:place)<-[:in_region_of]-(:place)<-[:stays_in]-(sub:subscriber)-[:was]->(:`profile`{name:"C9"})
RETURN p
Out[23]:
p | |
---|---|
0 | ("place-0" :place{name: "Fudan University", ci... |
1 | ("place-4" :place{name: "Jinmao Building", cit... |
2 | ("place-0" :place{name: "Fudan University", ci... |
3 | ("place-0" :place{name: "Fudan University", ci... |
4 | ("place-1" :place{name: "Shanghai Jiao Tong Un... |
5 | ("place-1" :place{name: "Shanghai Jiao Tong Un... |
In [24]:
%ng_draw
nebulagraph_draw.html
Out[24]:
In [25]:
!mv nebulagraph_draw.html ng_draw_2.3.2.html
2.4 画像:使用高性价比手机的学生,则是"学生智能"¶
In [26]:
%%ngql
MATCH (:profile{name: "Student"})<-[was_student:was]-(sub:subscriber)-[:owned_device]->(:device)-[:with_model]->(:device_model)-[:is_categorized_as]->(:product_category{name: "Cost-effective Product"})
WHERE was_student.`time` > datetime("2022-05-31 12:00:00")
RETURN id(sub) AS ID, sub.subscriber.name AS Name,
"Student Smart Phone" AS Profile ORDER BY ID
Out[26]:
ID | Name | profile | |
---|---|---|---|
0 | sub-0 | Tom Smith | Student Smart Phone |
1 | sub-1 | Emma Smith | Student Smart Phone |
2 | sub-10 | Mia Garcia | Student Smart Phone |
3 | sub-2 | Liam Johnson | Student Smart Phone |
4 | sub-5 | Ava Wilson | Student Smart Phone |
5 | sub-6 | Sophia Taylor | Student Smart Phone |
6 | sub-7 | Jackson Anderson | Student Smart Phone |
7 | sub-8 | Isabella Martinez | Student Smart Phone |
8 | sub-9 | Aiden Thompson | Student Smart Phone |
In [27]:
%%ngql
# 增加边:"学生智能" 画像
INSERT EDGE was (`time`) VALUES
"sub-0"->"profile-2":(datetime("2023-06-11 12:00:00")),
"sub-1"->"profile-2":(datetime("2023-06-21 13:01:30")),
"sub-10"->"profile-2":(datetime("2022-06-12 15:04:30")),
"sub-2"->"profile-2":(datetime("2023-06-30 14:03:00")),
"sub-5"->"profile-2":(datetime("2023-06-02 17:07:30")),
"sub-6"->"profile-2":(datetime("2023-06-12 18:09:00")),
"sub-7"->"profile-2":(datetime("2023-06-22 19:10:30")),
"sub-8"->"profile-2":(datetime("2023-06-02 20:12:00")),
"sub-9"->"profile-2":(datetime("2023-06-12 21:13:30"));
Out[27]:
In [28]:
%%ngql
MATCH p=(:profile{name: "Student Smart Phone"})<-[:was]-(sub:subscriber)-[:owned_device]->(:device)-[:with_model]->(:device_model)-[:is_categorized_as]->(:product_category{name: "Cost-effective Product"})
RETURN p
Out[28]:
p | |
---|---|
0 | ("profile-2" :profile{name: "Student Smart Pho... |
1 | ("profile-2" :profile{name: "Student Smart Pho... |
2 | ("profile-2" :profile{name: "Student Smart Pho... |
3 | ("profile-2" :profile{name: "Student Smart Pho... |
4 | ("profile-2" :profile{name: "Student Smart Pho... |
5 | ("profile-2" :profile{name: "Student Smart Pho... |
6 | ("profile-2" :profile{name: "Student Smart Pho... |
7 | ("profile-2" :profile{name: "Student Smart Pho... |
8 | ("profile-2" :profile{name: "Student Smart Pho... |
In [29]:
%ng_draw
nebulagraph_draw.html
Out[29]:
2.5 同时拥有”学生智能“与”C9高校”画像,那么他们是潜在高消费用户画像¶
In [30]:
%%ngql
MATCH (:profile{name: "Student Smart Phone"})<-[was:was]-(sub:subscriber)-[:was]->(:profile{name: "C9"})
RETURN id(sub) AS ID, sub.subscriber.name AS Name,
"Potential High-end Consumer" AS Profile ORDER BY ID
Out[30]:
ID | Name | profile | |
---|---|---|---|
0 | sub-0 | Tom Smith | Potential High-end Consumer |
1 | sub-1 | Emma Smith | Potential High-end Consumer |
2 | sub-10 | Mia Garcia | Potential High-end Consumer |
3 | sub-2 | Liam Johnson | Potential High-end Consumer |
In [31]:
%%ngql
# 增加边:潜在高端消费者 画像
INSERT EDGE was (`time`) VALUES
"sub-10"->"profile-3":(datetime("2023-06-11 12:00:00")),
"sub-1"->"profile-3":(datetime("2023-06-21 13:01:30")),
"sub-2"->"profile-3":(datetime("2023-06-30 14:03:00")),
"sub-3"->"profile-3":(datetime("2023-06-12 15:04:30"));
Out[31]:
In [32]:
%%ngql
# 图解释
MATCH p=(:profile)<-[:was]-(:subscriber)-[:was]->(profile:profile)
WHERE profile.profile.name IN ["Potential High-end Consumer"]
RETURN p
Out[32]:
p | |
---|---|
0 | ("profile-0" :profile{name: "Student"})<-[:was... |
1 | ("profile-1" :profile{name: "C9"})<-[:was@0{ti... |
2 | ("profile-0" :profile{name: "Student"})<-[:was... |
3 | ("profile-1" :profile{name: "C9"})<-[:was@0{ti... |
4 | ("profile-2" :profile{name: "Student Smart Pho... |
5 | ("profile-0" :profile{name: "Student"})<-[:was... |
6 | ("profile-1" :profile{name: "C9"})<-[:was@0{ti... |
7 | ("profile-2" :profile{name: "Student Smart Pho... |
8 | ("profile-0" :profile{name: "Student"})<-[:was... |
9 | ("profile-1" :profile{name: "C9"})<-[:was@0{ti... |
10 | ("profile-2" :profile{name: "Student Smart Pho... |
In [33]:
%ng_draw
nebulagraph_draw.html
Out[33]:
In [34]:
!mv nebulagraph_draw.html ng_draw_2.5.html
3. 图谱应用¶
3.1 毕业季学生智能画像的用户,推荐合约机¶
In [35]:
%%ngql
MATCH p=(:profile{name: "Student Smart Phone"})<-[:was]-(sub:subscriber)-[:owned_device]->(phone:device)
RETURN sub.subscriber.msisdn AS MSISDN, phone.device.model AS current_phone
Out[35]:
MSISDN | current_phone | |
---|---|---|
0 | 13900000008 | RedMi 8 |
1 | 13900000007 | RedMi 7 |
2 | 13900000006 | RedMi 6 |
3 | 13900000010 | iPhone 6 |
4 | 13900000003 | iPhone 8 |
5 | 13900000009 | RedMi 9 |
6 | 13900000011 | iPhone 7 |
7 | 13900000002 | iPhone 7 |
8 | 13900000001 | iPhone 6 |
In [36]:
%%ngql
# Graph Explanation
MATCH p=(:profile{name: "Student Smart Phone"})<-[:was]-(sub:subscriber)-[:owned_device]->(:device)-[:with_model]->(:device_model)-[:is_categorized_as]->(:product_category{name: "Cost-effective Product"})
RETURN p
Out[36]:
p | |
---|---|
0 | ("profile-2" :profile{name: "Student Smart Pho... |
1 | ("profile-2" :profile{name: "Student Smart Pho... |
2 | ("profile-2" :profile{name: "Student Smart Pho... |
3 | ("profile-2" :profile{name: "Student Smart Pho... |
4 | ("profile-2" :profile{name: "Student Smart Pho... |
5 | ("profile-2" :profile{name: "Student Smart Pho... |
6 | ("profile-2" :profile{name: "Student Smart Pho... |
7 | ("profile-2" :profile{name: "Student Smart Pho... |
8 | ("profile-2" :profile{name: "Student Smart Pho... |
In [37]:
%ng_draw
nebulagraph_draw.html
Out[37]:
In [39]:
!mv nebulagraph_draw.html ng_draw_3.1.html
3.2 曾经 C9 现在在 CDB 的高消费潜在用户适合被推荐高级服务¶
举例来说,一个 CBD 中的用户如下
In [40]:
# Create a folium map centered at CBD
map_graph = folium.Map(location=shagnhai_cbd, zoom_start=20)
points_subscriber = [
(31.235231, 121.505123, "sub-9"),
(31.235431, 121.506163, "sub-10"),
]
points_place = [
# JinMao Building
(31.2353, 121.5057, "Jinmao Building"),
]
draw_circle(map_graph, points_place, 0.1)
draw_points(map_graph, points_subscriber)
draw_points(map_graph, points_place, color='blue', icon='home')
draw_connections(map_graph, points_subscriber, points_place)
from IPython.display import display
display(map_graph)
Make this Notebook Trusted to load map: File -> Trust Notebook
可以看到,sub-9、sub-10 两个 CDB 区域长期漫游的用户,我们可以中通过曾经是 C9 并且“学生智能”的多维度画像,得出有限推荐高增值服务的目标用户
In [42]:
%%ngql
MATCH (sub:subscriber)-[:stays_in]->(place:place)
WHERE
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5057 31.2353)") # CBD
) < 5000
WITH sub, place
MATCH (:profile{name: "Student Smart Phone"})<-[was:was]-(sub:subscriber)-[:was]->(:profile{name: "C9"})
RETURN id(sub) AS ID, sub.subscriber.msisdn AS MSISDN, sub.subscriber.name AS Name
Out[42]:
ID | MSISDN | Name | |
---|---|---|---|
0 | sub-10 | 13900000011 | Mia Garcia |
In [43]:
%%ngql
# 图可解释
MATCH p=(sub:subscriber)-[:stays_in]->(place:place)
WHERE
ST_Distance(place.place.geo_point,
ST_GeogFromText("POINT (121.5057 31.2353)") # CBD
) < 5000
WITH sub, place, p
MATCH p1=(:profile{name: "Student Smart Phone"})<-[was:was]-(sub:subscriber)-[:was]->(:profile{name: "C9"})
OPTIONAL MATCH p2=(region:place)<-[:in_region_of]-(:place)<-[:stays_in]-(sub:subscriber)-[:owned_device]->(:device)-[:with_model]->(model:device_model)
RETURN p, p1, p2
Out[43]:
p | p1 | p2 | |
---|---|---|---|
0 | ("sub-10" :subscriber{msisdn: 13900000011, nam... | ("profile-2" :profile{name: "Student Smart Pho... | ("place-4" :place{name: "Jinmao Building", cit... |
1 | ("sub-10" :subscriber{msisdn: 13900000011, nam... | ("profile-2" :profile{name: "Student Smart Pho... | ("place-0" :place{name: "Fudan University", ci... |
In [44]:
%ng_draw
nebulagraph_draw.html
Out[44]:
In [45]:
!mv nebulagraph_draw.html ng_draw_3.2.html