上一篇我们通过分析 activity 表,并建立 meta_view_create_latest 视图的方式,解决了 market 表中没有上架时间的问题。
但对于 market 表,我们似乎忘记了一些事情。是的,每一次从合约获取数据进行保存,虽然保证了商品列表的最新、最全,但我们没有把“已取消”和“已成交”的商品信息从 market 表中删除掉。这显然是不合理的。
下面我们来解决这个问题。
- 找出“已取消”和“已成交”的时间点
为了找出最新的“已取消”时间和“已成交”时间,我们按上文介绍的方法分别创建2个新视图:meta_view_cancel_latest、meta_view_confirm_latest
- 对比时间点,找出应该从market表删除的数据
比较取消时间和创建时间,如果取消时间比创建时间靠后,则说明这条商品信息应该从market表中删除。以下语句用于创建新视图“meta_view_must_del_cancel”
```sql
SELECT c.contract,c.tokenID FROM (SELECT a.`contract`,a.`tokenID`,IF(a.`timestamp`>b.`timestamp`,1,0) must_del FROM meta_view_cancel_latest a LEFT JOIN meta_view_create_latest b ON a.`contract`=b.`contract` AND a.`tokenID`=b.`tokenID`) c WHERE c.must_del=1;
```
比较已经交时间和创建时间,如果已成交的时间比创建时间靠后,则说明这条商品信息应该从market表中删除。以下语句用于创建新视图“meta_view_must_del_confirm”
```sql
SELECT a.`contract`,a.`tokenID`,IF(a.`timestamp`>b.`timestamp`,1,0) must_del FROM meta_view_confirm_latest a LEFT JOIN meta_view_create_latest b ON a.`contract`=b.`contract` AND a.`tokenID`=b.`tokenID` HAVING must_del=1;
```
上面我们采用了两种方法进行筛选数据,感兴趣的同学可以测试比较一下两种方式的差异
- 数据量检测
接下来我们试着连表查询,看看因为“取消上架”导致应该删除的商品信息在market表中的数目对比。
```sql
SELECT * FROM meta_view_must_del_cancel a LEFT JOIN meta_market b ON a.`contract`=b.`collection` AND a.`tokenID`=b.`tokenID`;
```
我们发现,出现了很多的NULL的数据。即应该删除的信息比market表中要多。这说明,我们从合约获取上架商品列表数据时,已经有部分商品被已取消了。我们同步数据的程序运行时间晚于合约实际运行时间。此为正常现象,只需要在删除的时候将该数据过滤掉即可。
同样,因为“已成交”导致market表中多余的数据的查询方法如下:
```sql
SELECT * FROM meta_view_must_del_confirm a LEFT JOIN meta_market_copy b ON a.`contract`=b.`collection` AND a.`tokenID`=b.`tokenID`;
```
- 数据删除
最后,我们要将因“取消上架”和“已成交”导致的不应该在market列表中的数据清理掉。
```sql
DELETE b.* FROM meta_view_must_del_cancel a LEFT JOIN meta_market_copy b ON a.`contract`=b.`collection` AND a.`tokenID`=b.`tokenID` WHERE collection IS NOT NULL;
DELETE b.* FROM meta_view_must_del_confirm a LEFT JOIN meta_market_copy b ON a.`contract`=b.`collection` AND a.`tokenID`=b.`tokenID` WHERE collection IS NOT NULL;
```