malloy-imdb/transform.malloy at main · malloydata/malloy-imdb · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
##! experimental.persistence  experimental.virtual_source

source: raw_titles is imdb.sql("""
		SELECT * FROM
		read_csv_auto(
			-- 'https://datasets.imdbws.com/title.basics.tsv.gz'
			'data/title.basics.tsv.gz'
			, all_varchar=true, delim='\t', quote='',header=True)
	""")

source: raw_principals is imdb.sql("""
		SELECT * FROM
		read_csv_auto(
			-- 'https://datasets.imdbws.com/title.principals.tsv.gz'
			'data/title.principals.tsv.gz'
			, all_varchar=true, delim='\t', quote='',header=True)
	""")

source: raw_ratings is imdb.sql("""
		SELECT * FROM
		read_csv_auto(
			-- 'https://datasets.imdbws.com/title.ratings.tsv.gz'
			'data/title.ratings.tsv.gz'
			, all_varchar=true, delim='\t', quote='',header=True)
	""")

source: raw_crew is imdb.sql("""
		SELECT * FROM
		read_csv_auto(
			-- 'https://datasets.imdbws.com/title.crew.tsv.gz'
			'data/title.crew.tsv.gz'
			, all_varchar=true, delim='\t', quote='',header=True)
	""")
source: raw_names is imdb.sql("""
		SELECT * FROM
		read_csv_auto(
			-- 'https://datasets.imdbws.com/name.basics.tsv.gz'
			'data/name.basics.tsv.gz'
			, all_varchar=true, delim='\t', quote='',header=True)
	""")

type: title_type is {
	tconst:: string,
	isAdult:: string,
	originalTitle:: string,
	primaryTitle:: string,
	startYear:: number,
	endYear:: number,
	runtimeMinutes:: number,
	genres:: string[],
	directors:: string[],
	writers:: string[],
	averageRating:: number,
	numVotes:: number,
	overall_rank:: number,
	year_rank:: number
}

#@ persist name="titles"
source: titles_base is raw_titles -> {
	join_one: raw_ratings on tconst = raw_ratings.tconst
	join_one: raw_crew on tconst = raw_crew.tconst
	where: raw_ratings.numVotes::number > 5000 and titleType = 'movie'
	select:
		tconst
		isAdult, originalTitle, primaryTitle
		startYear is startYear:::number
		endYear is endYear:::number
		runtimeMinutes is runtimeMinutes:::number
		genres is str_split!string[](genres,',')
		directors is str_split!string[](raw_crew.directors,',')
		writers is str_split!string[](raw_crew.writers,',')
		averageRating is raw_ratings.averageRating:::number
		numVotes is raw_ratings.numVotes:::number
	calculate:
		overall_rank is rank() {order_by: averageRating desc}
		year_rank is rank() {partition_by: startYear order_by: averageRating desc}
}::title_type extend {
	view: test is {
		select: *
		limit: 10
	}
}

type: principal_type is {
	tconst:: string,
	ordering:: number,
	nconst:: string,
	category:: string,
	job:: string,
	characters:: string[]
}

#@ persist name="principals"
source: principals_base is raw_principals -> {
	join_one: titles is titles_base on tconst = titles.tconst
	where: titles.tconst is not null
	select:
		tconst
		ordering is ordering::number
		nconst
		category
		job
		characters is str_split!string[](
	regexp_replace!(characters,'[\[\]\"]','','g'),
	',')
}::principal_type extend {
	view: test is {
		select: *
		limit: 10
	}
}

type: year_ratings is {
  startYear:: number,
  year_ratings_acting:: number,
  year_ratings_directing:: number,
  year_rank:: number
}

type: person_type is {
  nconst:: string,
  birthYear:: number,
  deathYear:: number,
  primaryName:: string,
  primaryProfession:: string,
  lifetime_rank_acting:: number,
  lifetime_ratings_acting:: number,
  lifetime_rank_directing:: number,
  lifetime_ratings_directing:: number,
  by_year:: year_ratings[]
}

#@ persist name="names"
source: people_base is   raw_names -> {
	join_many: principals is principals_base on nconst = principals.nconst
	join_one: titles is titles_base on principals.tconst = titles.tconst
	where: principals.nconst is not null
	group_by:
		nconst
		primaryName
		primaryProfession
		birthYear is birthYear:::number
		deathYear is deathYear:::number
		titles.startYear
	aggregate:
		year_ratings_acting is titles.numVotes.sum() {where: principals.category = 'actor' | 'actress' }
		year_ratings_directing is titles.numVotes.sum() {where: principals.category = 'director' }
	calculate:
		year_rank is rank() {partition_by: startYear order_by: year_ratings_acting desc}
	}
	-> {
		group_by:
			nconst
			primaryName
			primaryProfession
			birthYear is birthYear:::number
			deathYear is deathYear:::number
		aggregate:
		  lifetime_ratings_acting is year_ratings_acting.sum()
		  lifetime_ratings_directing is year_ratings_directing.sum()
		calculate:
		  lifetime_rank_acting is rank() {order_by: lifetime_ratings_acting desc}
		  lifetime_rank_directing is rank() {order_by: lifetime_ratings_directing desc}
		nest: by_year is {
			group_by:
				startYear
				year_ratings_acting
				year_ratings_directing
				year_rank
		}
		order_by: nconst
	}::person_type