Wordnet sqlite同义词和示例 [英] Wordnet sqlite Synonyms and Samples

问题描述

我正在尝试获取给定wordid的同义词和样本列表.经过大量的试验和错误，我可以获取所有同义词集的样本，但不能获取实际的同义词.这是我的查询，它为我提供以下结果.

I am trying to get the list of Synonyms and Samples given the wordid. After a lot of trial and error I can get the samples for all the synsets but not the actual synonyms. Here is my query which gives me the following results.

select senses.wordid, senses.synsetid, senses.sensekey, synsets.definition FROM感觉 LEFT OUTER JOIN同义词ON senses.synsetid = synsets.synsetid where senses.wordid = 79459

select senses.wordid, senses.synsetid, senses.sensekey, synsets.definition FROMsenses LEFT OUTER JOINsynsetsON senses.synsetid = synsets.synsetid where senses.wordid = 79459

我知道您可以通过将synsetid提交回Senses表来获得同义词，该语义表为您提供唯一的wordid和sensekey，然后您可以将它们与words表结合在一起.我的问题是我似乎无法建立该查询.

I know you can get the synonyms by submiting the synsetid back to the senses table which gives you unique wordid and sensekey which you can then join with the words table. My problem is I can't seem to build that query.

如果可能的话，我想获得这些专栏.如果不是同义词集，引理和定义就行.当前数据库是mySql，但我希望答案也适用于sqlite，因为我正在将其用于android APP.

I would like to get these columns if possible. If not synsetid, lemma and definition would do. The current database is mySql but I am hoping the answer would also be applicable to sqlite, since I am using this for an android APP.

wordid，引理，Senseid，同义词集，定义

wordid, lemma, senseid, synsetid, definition

模式:

CREATE TABLE `synsets` (
  `synsetid` int(10) unsigned NOT NULL DEFAULT '0',
  `pos` enum('n','v','a','r','s') NOT NULL,
  `definition` mediumtext,
  PRIMARY KEY (`synsetid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `words` (
  `wordid` int(10) unsigned NOT NULL DEFAULT '0',
  `lemma` varchar(80) NOT NULL,
  `mantiq` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`wordid`),
  UNIQUE KEY `unq_words_lemma` (`lemma`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `senses` (
  `wordid` int(10) unsigned NOT NULL DEFAULT '0',
  `synsetid` int(10) unsigned NOT NULL DEFAULT '0',
  `senseid` int(10) unsigned DEFAULT NULL,
  `sensekey` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`wordid`,`synsetid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `samples` (
  `synsetid` int(10) unsigned NOT NULL DEFAULT '0',
  `sampleid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `sample` mediumtext NOT NULL,
  PRIMARY KEY (`synsetid`,`sampleid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

到数据库的链接: https://cloud.generatedesign.com/index.php /s/LA2G8ZvqNClqHFN

推荐答案

我不确定我是否完全理解这个问题，但是这种方法行不通吗?

I'm not sure I exactly understand the question, but wouldn't something like this work?

SELECT s1.wordid, s1.synsetid, s1.sensekey, synsets.definition
   , s2.wordid AS matchedWordID, w.*  -- Additional info not from question's query
FROM senses AS s1
   LEFT JOIN synsets ON s1.synsetid = synsets.synsetid
   LEFT JOIN senses AS s2 ON s1.synsetid = s2.synsetid AND s1.wordid <> s2.wordid
   LEFT JOIN words AS w ON s2.wordid = w.wordid
WHERE s1.wordid = 79459
;

注意:...只是您实际想要的字段列表的简写.

Note: ... is just short hand for the list of fields you actually want.

注#2:当然，您可以使用synsets引用加入样本，但是请记住，每个单词对和样本都将重复结果；如果某些单词对是多种含义的同义词，则有可能会重复.

Note#2: You can of course JOIN to samples using the synsets reference, but keep in mind the results would be repeated for every word pair and sample; and it is possible some word pairs may be repeated if they are synonyms in multiple meanings.

这篇关于Wordnet sqlite同义词和示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！