《EdgeDB 易经》 · 第十章

# 惠特比的可怕事件​

TuplesComputed PropertiesMath

Copy
```type City extending Place {
population: int64;
}
```

• Buda-Pesth (Budapest): 402706

• London: 3500000

• Munich: 230023

• Whitby: 14400

• Bistritz (Bistrița): 9100

Copy
```insert City {
name := 'Whitby',
population := 14400,
important_places := ['Whitby Abbey']
};
```

`('Buda-Pesth', 402706), ('London', 3500000), ('Munich', 230023), ('Bistritz', 9100)`

Copy
```select NPC.name[0:10];
```

```{
'The innkee',
'Mina Murra',
'Jonathan H',
'Lucy Weste',
'John Sewar',
'Quincey Mo',
'Arthur Hol',
'Renfield',
}
```

Copy
```select NPC.name[2:-2];
```

```{
'e innkeep',
'na Murr',
'nathan Hark',
'cy Westen',
'hn Sewa',
'incey Morr',
'thur Holmwo',
'nfie',
}
```

Copy
```select {('Bistritz', 9100, cal::to_local_date(1893, 5, 6)), ('Munich', 230023, cal::to_local_date(1893, 5, 8))};
```

```{
('Bistritz', 9100, <cal::local_date>'1893-05-06'),
('Munich', 230023, <cal::local_date>'1893-05-08'),
}
```

Copy
```select {(1, 2, 3), (4, 5, '6')};
```

```ERROR: QueryError: operator 'union' cannot be applied to operands of type 'tuple<std::int64, std::int64, std::int64>' and 'tuple<std::int64, std::int64, std::str>'
Hint: Consider using an explicit type cast or a conversion function.
```

Copy
```for data in {('Buda-Pesth', 402706), ('London', 3500000), ('Munich', 230023), ('Bistritz', 9100)}
union (
update City filter .name = data.0
set {
population := data.1
}
);
```

Copy
```with london := ('London', 3500000),
select <tuple<json, int32>>london;
```

```{("\"London\"", 3500000)}
```

Copy
```with london := <tuple<json, float64>>('London', 3500000),
select (london.0, london.1 / 9);
```

Copy
```select City {
name,
population
} order by .population desc;
```

```{
default::City {name: 'London', population: 3500000},
default::City {name: 'Buda-Pesth', population: 402706},
default::City {name: 'Munich', population: 230023},
default::City {name: 'Whitby', population: 14400},
default::City {name: 'Bistritz', population: 9100},
}
```

Copy
```with cities := City.population
select (
'Number of cities: ' ++ <str>count(cities),
'All cities have more than 50,000 people: ' ++ <str>all(cities > 50000),
'Total population: ' ++ <str>sum(cities),
'Smallest and largest population: ' ++ <str>min(cities) ++ ', ' ++ <str>max(cities),
'Average population: ' ++ <str>math::mean(cities),
'At least one city has more than 5 million people: ' ++ <str>any(cities > 5000000),
'Standard deviation: ' ++ <str>math::stddev(cities)
);
```

• `count()` 计算项目（item）的数量，

• `all()` 如果所有项目都匹配，则返回 `{true}`，否则返回 `{false}`

• `sum()` 对所有项目进行相加，

• `max()` 给出数值最大的项目，

• `min()` 给出数值最小的项目，

• `math::mean()` 给出所有项目的平均值，

• `any()` 只要有一个项目匹配，则返回 `{true}`，否则返回 `{false}`

• `math::stddev()` 给出所有项目的标准差。

```{
(
'Number of cities: 5',
'All cities have more than 50,000 people: false',
'Total population: 4156229',
'Smallest and largest population: 9100, 3500000',
'Average population: 831245.8',
'At least one city has more than 5 million people: false',
'Standard deviation: 1500876.8248',
),
}
```

```ERROR: InvalidReferenceError: function 'default::mean' does not exist
```

Copy
```with cities := City.population,
module math
select (
'Number of cities: ' ++ <str>count(cities),
'All cities have more than 50,000 people: ' ++ <str>all(cities > 50000),
'Total population: ' ++ <str>sum(cities),
'Smallest and largest population: ' ++ <str>min(cities) ++ ', ' ++ <str>max(cities),
'Average population: ' ++ <str>mean(cities),
'At least one city has more than 5 million people: ' ++ <str>any(cities > 5000000),
'Standard deviation: ' ++ <str>stddev(cities)
);
```

Copy
```with M as module math,
select M::mean(City.population);
```

```Letter, Abraham Van Helsing, M. D., D. Ph., D. Lit., etc., etc., to Dr. Seward.

“2 September.

“My good Friend,—
```

`Abraham Van Helsing, M. D., D. Ph., D. Lit., etc., etc.` 这部分很有趣。这可能是个不错的时机让我们进一步考虑 `Person` 类型中的 `name` 属性。现在我们的 `name` 属性只是一个字符串，但是我们的角色会拥有不同类别的称呼，并按以下顺序进行排列：

Title（头衔） | First name（名） | Last name（姓） | Degree（学位）

Copy
```title: str;
degrees: str;
property conversational_name := .title ++ ' ' ++ .name if exists .title else .name;
property pen_name := .name ++ ', ' ++ .degrees if exists .degrees else .name;
```

Copy
```insert NPC {
name := 'Abraham Van Helsing',
title := 'Dr.',
degrees := 'M.D., Ph. D. Lit., etc.'
};
```

Copy
```with helsing := (select NPC filter .name ilike '%helsing%')
select (
'There goes ' ++ helsing.name ++ '.',
'I say! Are you ' ++ helsing.conversational_name ++ '?',
'Letter from ' ++ helsing.pen_name ++ ',\n\tI am sorry to say that I bring bad news about Lucy.'
);
```

```{
(
'There goes Abraham Van Helsing.',
'I say! Are you Dr. Abraham Van Helsing?',
'Letter from Abraham Van Helsing, M.D., Ph. D. Lit., etc.,
I am sorry to say that I bring bad news about Lucy.',
),
}
```

PS：在有真实”用户“的标准数据库中，收集用户信息要简单得多：因为我们可以让用户自己输入他们的名字、姓氏等，并使每个部分都成为一个属性。

Copy
```with helsing := (select NPC filter .name ilike '%helsing%')
select (
'There goes ' ++ helsing.name ++ '.',
'I say! Are you ' ++ helsing.conversational_name ++ '?',
'Letter from ' ++ helsing.pen_name ++ r',\n\tI am sorry to say that I bring bad news about Lucy.'
);
```

```{
(
'There goes Abraham Van Helsing.',
'I say! Are you Dr. Abraham Van Helsing?',
'Letter from Abraham Van Helsing, M.D., Ph. D. Lit., etc.,\\n\\tI am sorry to say that I bring bad news about Lucy.',
),
}
```

Copy
```select \$\$
"Dr. Van Helsing would like to tell "them"
about "vampires" and how to "kill" them,
but he'd sound crazy."
\$\$;
```

Copy
```insert NPC {
name := 'Johnny'
};
```

1880 年慕尼黑（Munich）的人口为 230,023，五年后为 261,023。假设我们正在更新 `City` 数据，且一些城市可能已经在数据库里存在了，而另一些城市可能尚未创建。那么，`insert` 慕尼黑（Munich）的语句应如下所示：

Copy
```insert City {
name := 'Munich',
population := 261023,
} unless conflict on .name
else (
update City
set {
population := 261023,
}
);
```

Copy
```insert City {
name := 'Munich'
}
```

Copy
```else (
update City
set {
population := 261023
}
);
```

→ 点击这里查看到第 10 章为止的所有代码

1. 尝试通过一个插入语句插入两个 `NPC` 类型的对象，其中包含 `name`, `first_appearance``last_appearance` 信息。

`{('Jimmy the Bartender', '1893-09-10', '1893-09-11'), ('Some friend of Jonathan Harker', '1893-07-08', '1893-07-09')}`

查看答案
2. 这里还有两个要插入的 `NPC`，后者的最后有一个空集（因为她还没有死）。插入它们时，我们会遇到什么问题？

`{('Dracula\'s Castle visitor', '1893-09-10', '1893-09-11'), ('Old lady from Bistritz', '1893-05-08', {})}`

查看答案
3. 你将如何按人物姓名的最后一个字母对 `Person` 类型的对象进行排序？

查看答案
4. 尝试插入一个名为 `''``NPC`。现在，你将如何在问题 3 中执行相同的查询？

提示：`''` 的长度为 0，这可能会有问题。

查看答案
5. 范海辛医生（Dr. Van Helsing ）有一份 `MinorVampire` 的名单，上面有他们的名字和力量。我们的数据库中已经有一些 `MinorVampire` 了。如果对象已经存在，你将如何在确保 `update` 的同时 `insert` 不存在的？

查看答案