User talk:Kipcool/stats

scripts

list of expressions

sql query, fast

USE omegawiki
SELECT  spelling, language_id
FROM uw_expression
WHERE expression_id IN
(
SELECT DISTINCT expression_id
FROM uw_syntrans
WHERE remove_transaction_id IS NULL
) ;

remark: the number of expressions obtained that way is one for each language: the word "toto" will appear twice if it exists in 2 languages, but only once if it has 2 definitions in one language. In the stats http://www.omegawiki.org/extensions/Wikidata/util/stats.php , remove_transaction_id is not taken correctly into account, therefore displaying a wrong higher number of expressions:

USE omegawiki
SELECT  spelling, language_id
FROM uw_expression
WHERE remove_transaction_id IS NULL ; <== this is wrong, it is not the expression itself which is deleted, but the link between an expression and a DM, i.e. in uw_syntrans

list of DMs

sql query

The right way to do it:

USE omegawiki
SELECT DISTINCT uw_syntrans.defined_meaning_id
FROM uw_syntrans, uw_expression
WHERE uw_syntrans.remove_transaction_id IS NULL
AND uw_expression.remove_transaction_id IS NULL
AND uw_syntrans.expression_id = uw_expression.expression_id ;

The wrong way, which will count also the deleted DMs ([1]):

USE omegawiki
SELECT DISTINCT defined_meaning_id
FROM uw_defined_meaning
WHERE remove_transaction_id IS NULL ;

DM's

The number of DM's on Google is also about the same as your count (even a bit higher)

date	#Expression	#DefinedMeanings	Yahoo: [2],	Google EXPR: [3]	Google DM: [4]
23 jan 2007	179'890	12'470	21'700	172'000	12'600

HenkvD 15:07, 23 January 2007 (EST)

Updating?

I found that when I was actively adding Khmer synonyms and definitions that seeing the numbers mount up in your statistics was very gratifying. I suppose I could figure out how to run the queries posted here, but that's no real replacement for checking your page weekly. Won't you resume running (and posting) these statistics? Rsperberg 08:54, 11 June 2008 (EDT)

I saw you message only today... I will see what I can do, but from what I heard, the dump is broken at the moment. Kipcool 12:32, 3 July 2008 (EDT)

dump repaired, stats updated. Kipcool 14:58, 8 July 2008 (EDT)