Difference between revisions of "Data Donations"

From Openresearch
Jump to: navigation, search
Line 1: Line 1:
 
https://diagrams.bitplan.com/render/svg/0x28a15f08.svg
 
https://diagrams.bitplan.com/render/svg/0x28a15f08.svg
−
 
+
https://diagrams.bitplan.com/render/png/0x28a15f08.png
  
 
= Generalized View =
 
= Generalized View =

Revision as of 08:09, 21 February 2021

https://diagrams.bitplan.com/render/svg/0x28a15f08.svg https://diagrams.bitplan.com/render/png/0x28a15f08.png

Generalized View

drop view if exists event;
create view event as 
   select eventId,title,url,lookupAcronym,acronym,source,year from event_CEURWS
union 
   select eventId,title,url,lookupAcronym,acronym,source,year from event_crossref
union 
   select eventId,title,url,lookupAcronym,acronym,source,year from event_confref
union 
   select eventId,title,url,lookupAcronym,acronym,source,year from event_dblp   
union 
   select eventId,title,url,lookupAcronym,acronym,source,year from event_gnd     
union
   select eventId,title,url,lookupAcronym,acronym,source,year from event_or
union
   select eventId,title,url,lookupAcronym,acronym,source,year from event_wikicfp
union 
   select eventId,title,url,lookupAcronym,acronym,source,year from event_wikidata;
select count(*) from event;
select count(*),lookupAcronym 
from event
group by lookupAcronym 
having count(*)>2
order by 1 desc;

236886

List of Data Donations

Queries

Count of Events grouped by source

query

select 
    source,count(*) as count 
    from event
    group by source
union 
select 
    "TOTAL",count(*) as count
    from event
    order by count

result

source count
CEUR-WS 2656
or 8824
wikidata 15951
confref 37945
dblp 43976
crossref 46099
wikicfp 81966
TOTAL 237417

Crossref number usage

query

select count(*) as count,cast(number as integer) as enum,number from event_crossref
where number is not null
group by enum,number
having count>1
order by enum

result

count enum number
5 0 .
40 0 0
3 0 ??
13 0 I
11 0 II
9 0 III
9 0 IV
4 0 IX
2 0 V
5 0 VI
4 0 VII
4 0 VIII
5 0 X
2 0 XIII
4 0 none
843 1 1
2 1 1st
705 2 2
2 2 2 & 3
3 2 2nd
555 3 3
2 3 3nd
449 4 4
400 5 5
360 6 6
307 7 7
2 7 7th
290 8 8
2 9 09
250 9 9
248 10 10
190 11 11
166 12 12
145 13 13
124 14 14
109 15 15
108 16 16
106 17 17
95 18 18
85 19 19
72 20 20
71 21 21
65 22 22
60 23 23
52 24 24
38 25 25
35 26 26
34 27 27
35 28 28
27 29 29
22 30 30
23 31 31
22 32 32
22 33 33
15 34 34
14 35 35
18 36 36
12 37 37
15 38 38
10 39 39
13 40 40
5 41 41
9 42 42
8 43 43
7 44 44
9 45 45
10 46 46
8 47 47
5 48 48
6 49 49
6 50 50
3 51 51
4 52 52
2 53 53
2 69 69
2 71 71
2 72 72
2 74 74
2 1952 1952
2 1967 1967
2 1970 1970
3 1971 1971
2 1973 1973
3 1975 1975
3 1976 1976
2 1977 1977
2 1978 1978
2 1979 1979
2 1980 1980
6 1982 1982
3 1983 1983
2 1984 1984
7 1985 1985
17 1986 1986
14 1987 1987
17 1988 1988
19 1989 1989
24 1990 1990
20 1991 1991
23 1992 1992
27 1993 1993
24 1994 1994
24 1995 1995
27 1996 1996
33 1997 1997
37 1998 1998
37 1999 1999
55 2000 2000
36 2001 2001
37 2002 2002
59 2003 2003
52 2004 2004
66 2005 2005
105 2006 2006
94 2007 2007
108 2008 2008
135 2009 2009
113 2010 2010
120 2011 2011
134 2012 2012
138 2013 2013
162 2014 2014
143 2015 2015
169 2016 2016
134 2017 2017
122 2018 2018
77 2019 2019
2 2020 2020

Crossref events with long numbers

query

select number,cast(number as integer) as enum,year,title 
from event_crossref
where  enum> 53 and enum<1952

result

number enum year title
69 69 2014 ABM Proceedings
55 55 2018 ABM Proceedings
71 71 None Proceedings of the 71st International Symposium on Molecular Spectroscopy
74 74 None Proceedings of the 74th International Symposium on Molecular Spectroscopy
72 72 None Proceedings of the 72nd International Symposium on Molecular Spectroscopy
71 71 2016 Anais do Congresso Anual da ABM
72 72 2017 Anais do Congresso Anual da ABM
54 54 2017 Anais do Seminário de Laminação e Conformação
69 69 None Proceedings of the 69th International Symposium on Molecular Spectroscopy
70 70 None Proceedings of the 70th International Symposium on Molecular Spectroscopy
206 206 2006 Proceedings of the 2006 International Conference on Privacy, Security and Trust Bridge the Gap Between PST Technologies and Business Services - PST '06
76 76 2019 ITAA Annual Conference Proceedings
74 74 2019 ABM Proceedings
101 101 2014 ROSCon Hong Kong
103 103 2019 ROSCon France
73 73 None Proceedings of the 73rd International Symposium on Molecular Spectroscopy

WikiCfP year distribution

query

select year,count(*) as count
from event_wikicfp
group by year
order by year

result

year count
None 8235
35 1
1920 1
2000 20
2001 5
2002 1
2004 1
2005 2
2007 462
2008 2177
2009 2541
2010 4069
2011 5363
2012 5298
2013 5153
2014 5742
2015 5721
2016 6346
2017 7355
2018 8207
2019 8006
2020 6237
2021 1014
2022 2
2024 1
2025 1
2026 3
2081 1
2091 1

wikidata enums and ordinals

query

select count(*) as count,enum,ordinal 
from event_wikidata 
where enum is not null
group by enum,ordinal
union 
select count(*) as count,'total',''
from event_wikidata 
where enum is not null
order by 1 desc limit 20

result

count enum ordinal
6668 total
343 2nd 2
276 3rd 3
265 4th 4
261 First 1
248 5th 5
230 1st 1
206 Second 2
180 Third 3
175 6th 6
173 1 1
168 2 2
156 7th 7
151 8th 8
134 9th 9
124 Fourth 4
122 10th 10
110 11th 11
101 15th 15
101 Fifth 5

Event View Schema

query

pragma table_info('event');

result

cid name type notnull dflt_value pk
0 eventId TEXT 0 None 0
1 title TEXT 0 None 0
2 url TEXT 0 None 0
3 lookupAcronym TEXT 0 None 0
4 acronym TEXT 0 None 0
5 source TEXT 0 None 0

Event_CEURWS Schema

query

pragma table_info('event_CEURWS');

result

cid name type notnull dflt_value pk
0 acronym TEXT 0 None 0
1 country TEXT 0 None 0
2 enum TEXT 0 None 0
3 eventType TEXT 0 None 0
4 lookupAcronym TEXT 0 None 0
5 scope TEXT 0 None 0
6 topic TEXT 0 None 0
7 title TEXT 0 None 0
8 source TEXT 0 None 0
9 eventId TEXT 0 None 1
10 url TEXT 0 None 0
11 city TEXT 0 None 0
12 prefix TEXT 0 None 0
13 province TEXT 0 None 0
14 location TEXT 0 None 0
15 daterange TEXT 0 None 0
16 extract TEXT 0 None 0

Event_confref Schema

query

pragma table_info('event_confref');

result

cid name type notnull dflt_value pk
0 acronym TEXT 0 None 0
1 city TEXT 0 None 0
2 country TEXT 0 None 0
3 eventId TEXT 0 None 1
4 year INTEGER 0 None 0
5 startDate TEXT 0 None 0
6 endDate TEXT 0 None 0
7 submissionExtended BOOLEAN 0 None 0
8 title TEXT 0 None 0
9 lookupAcronym TEXT 0 None 0
10 source TEXT 0 None 0
11 url TEXT 0 None 0

Event_dblp Schema

query

pragma table_info('event_dblp');

result

cid name type notnull dflt_value pk
0 acronym TEXT 0 None 0
1 editor TEXT 0 None 0
2 title TEXT 0 None 0
3 booktitle TEXT 0 None 0
4 year TEXT 0 None 0
5 publisher TEXT 0 None 0
6 isbn TEXT 0 None 0
7 series TEXT 0 None 0
8 volume TEXT 0 None 0
9 ee TEXT 0 None 0
10 url TEXT 0 None 0
11 eventId TEXT 0 None 1
12 mdate TEXT 0 None 0
13 lookupAcronym TEXT 0 None 0
14 source TEXT 0 None 0
15 editorOrcid TEXT 0 None 0

Event_crossref Schema

query

pragma table_info('event_crossref');

result

cid name type notnull dflt_value pk
0 name TEXT 0 None 0
1 location TEXT 0 None 0
2 source TEXT 0 None 0
3 title TEXT 0 None 0
4 eventId TEXT 0 None 1
5 url TEXT 0 None 0
6 acronym TEXT 0 None 0
7 sponsor TEXT 0 None 0
8 number TEXT 0 None 0
9 startDate DATE 0 None 0
10 year INTEGER 0 None 0
11 month INTEGER 0 None 0
12 endDate DATE 0 None 0
13 lookupAcronym TEXT 0 None 0

Event_or Schema

query

pragma table_info('event_or');

result

cid name type notnull dflt_value pk
0 homepage TEXT 0 None 0
1 acronym TEXT 0 None 0
2 city TEXT 0 None 0
3 event TEXT 0 None 0
4 series TEXT 0 None 0
5 title TEXT 0 None 0
6 start_date TIMESTAMP 0 None 0
7 end_date TIMESTAMP 0 None 0
8 creation_date TIMESTAMP 0 None 0
9 modification_date TIMESTAMP 0 None 0
10 eventId TEXT 0 None 1
11 url TEXT 0 None 0
12 lookupAcronym TEXT 0 None 0
13 source TEXT 0 None 0
14 country TEXT 0 None 0

Event_wikicfp Schema

query

pragma table_info('event_wikicfp');

result

cid name type notnull dflt_value pk
0 Notification_Due DATE 0 None 0
1 Submission_Deadline DATE 0 None 0
2 acronym TEXT 0 None 0
3 deleted BOOLEAN 0 None 0
4 endDate DATE 0 None 0
5 eventId TEXT 0 None 1
6 eventType TEXT 0 None 0
7 locality TEXT 0 None 0
8 lookupAcronym TEXT 0 None 0
9 startDate DATE 0 None 0
10 title TEXT 0 None 0
11 wikiCFPId INTEGER 0 None 0
12 source TEXT 0 None 0
13 url TEXT 0 None 0

Event_wikidata Schema

query

pragma table_info('event_wikidata');

result

cid name type notnull dflt_value pk
0 acronym TEXT 0 None 0
1 description TEXT 0 None 0
2 eventType TEXT 0 None 0
3 location TEXT 0 None 0
4 lookupAcronym TEXT 0 None 0
5 scope TEXT 0 None 0
6 topic TEXT 0 None 0
7 title TEXT 0 None 0
8 source TEXT 0 None 0
9 eventId TEXT 0 None 1
10 url TEXT 0 None 0
11 enum TEXT 0 None 0
12 country TEXT 0 None 0
13 daterange TEXT 0 None 0
14 month TEXT 0 None 0
15 prefix TEXT 0 None 0
16 frequency TEXT 0 None 0
17 extract TEXT 0 None 0
18 year TEXT 0 None 0
19 city TEXT 0 None 0
Retrieved from "https://www.openresearch.org/mediawiki/index.php?title=Data_Donations&oldid=230135"