Untitled query #17569

This query is marked as a draft This query has been published by Larske.
SQL

USE svwiki_p;

SHOW tables;

SELECT page_title, pp_propname, page_len, page_id, pp_page FROM page_props INNER JOIN page on pp_page=page_id WHERE 
page_len <
/*
(
SELECT MIN(page_len) AS sidstorlek_min FROM page_props INNER JOIN page ON pp_page=page_id
WHERE pp_propname LIKE 'wikibase-badge-Q17437798' AND pp_value=1
)
*/
17169
AND pp_propname LIKE 'wikibase-badge-Q17437796' AND pp_value=1
ORDER BY page_len
LIMIT 100

/*
17437796     |
| wikibase-badge-Q17437798     |
| wikibase-badge-Q17506997     |
| wikibase-badge-Q17580674     |
| wikibase-badge-Q20748091     |
| wikibase-badge-Q20748092     |
| wikibase-badge-Q20748093
*/

/*
SELECT pp_propname, COUNT(*) AS antal, AVG(page_len) AS sidstorlek_medel, MIN(page_len) AS sidstorlek_min, MAX(page_len) AS sidstorlek_max FROM page_props INNER JOIN page ON pp_page=page_id
*/


/*
DESCRIBE heartbeat_p.heartbeat;
SELECT * FROM heartbeat_p.heartbeat;
DESCRIBE meta_p.wiki;
SELECT * FROM meta_p.wiki;
SELECT lag FROM heartbeat_p.heartbeat JOIN meta_p.wiki ON CONCAT(shard, '.labsdb') = slice WHERE dbname = 'svwiki';
*/

/*
SELECT concat('[[', rc_title, ']]') AS sida, SUM(rc_new_len-rc_old_len) AS tillagt FROM recentchanges 
WHERE rc_user_text='Larske' 
AND rc_namespace=0
GROUP BY sida
ORDER BY tillagt DESC
LIMIT 100
*/

/*
DESCRIBE categorylinks;
SELECT page_title, cl_to, cl_timestamp, cl_sortkey, cl_sortkey_prefix, cl_collation, cl_type FROM categorylinks 
INNER JOIN page on cl_from=page_id
ORDER BY cl_timestamp DESC 
LIMIT 1000
*/

/*
DESCRIBE geo_tags;
*/

/*SELECT CONCAT('[[', page_title, ']]'), CONCAT('{{coord|', gt_lat, '|', gt_lon, '|display=inline}}')*/ 

/*
SELECT page_title, gt_lat, gt_lon
FROM geo_tags INNER JOIN pagelinks ON gt_page_id=pl_from 
INNER JOIN page ON pl_from=page_id 
*/

/*INNER JOIN categorylinks ON pl_from=cl_from*/

/*
INNER JOIN templatelinks ON tl_from=gt_page_id 
*/

/*WHERE pl_title='Tropikal' AND tl_title='Paghimo_ni_bot'*/

/*
WHERE pl_title='Tropiskt_klimat' AND tl_title='Robotskapad' /*  # AND gt_lat<50.0 AND gt_lat>-50 */


/* cl_to = 'Robotskapade_Storbritannienartiklar' */

/*
ORDER BY gt_lat, gt_lon
*/

/*
SELECT pp_propname, page_title FROM page_props INNER JOIN page ON pp_page=page_id 
WHERE pp_propname LIKE 'wikibase-badge%'
ORDER BY pp_propname, page_title
LIMIT 2000
*/
/*
DESCRIBE category;

SELECT page_title FROM  page LEFT OUTER JOIN category ON page_title=cat_title WHERE cat_title IS NULL AND page_namespace=14
LIMIT 100
*/

/*
SELECT * FROM recentchanges INNER JOIN revision ON rc_cur_id=rev_page  INNER JOIN text on old_id=rev_page  WHERE 
rc_user_text='LarskeBot' AND rc_timestamp > '20170702204500' AND rc_timestamp < '20170702205130' AND rc_namespace = 0

LIMIT 1000
*/

/*
DESCRIBE geo_tags;

SELECT page_title, gt_lat, gt_lon, gt_name FROM geo_tags INNER JOIN page ON gt_page_id=page_id WHERE
page_title='Sandlåda_för_Wikidata/Larske'
*

/*SELECT CONCAT('[[',pl_title,']]') AS Titel, CONCAT('{{Nts|',COUNT(*),'}}') AS Antal FROM pagelinks WHERE
pl_title LIKE '%(huvudstaden)' AND pl_namespace=0 AND pl_from_namespace=0
GROUP BY pl_title
ORDER BY pl_title

*/

/*
SELECT rev_user_text, user_editcount, user_registration, MAX(rev_timestamp) AS senaste FROM user INNER JOIN revision on user_id=rev_user
WHERE user_registration > '20160101000000' AND user_registration < '20170101000000'
GROUP BY rev_user_text
ORDER BY user_editcount DESC
*/

/*USE wikidatawiki_p;

SHOW tables;

DESCRIBE recentchanges;

SELECT rc_user_text, SUM(IF(true,1,0)) AS Totalt, SUM(IF(rc_title='Q4115189',1,0)) AS Sandlada, SUM(IF(rc_title='Q13406268',1,0)) AS Sandlada2, SUM(IF(rc_title='Q15397819',1,0)) AS Sandlada3 FROM recentchanges
WHERE rc_title IN ('Q4115189', 'Q13406268', 'Q15397819')
/*
AND rc_timestamp > '20170601000000'
*/
/*
GROUP BY rc_user_text
UNION ALL
SELECT "total", SUM(IF(true,1,0)) AS Totalt, SUM(IF(rc_title='Q4115189',1,0)) AS Sandlada, SUM(IF(rc_title='Q13406268',1,0)) AS Sandlada2, SUM(IF(rc_title='Q15397819',1,0)) AS Sandlada3 FROM recentchanges
WHERE rc_title IN ('Q4115189', 'Q13406268', 'Q15397819')
*/
/*
SELECT cl_to, COUNT(DISTINCT cl_from) AS antal_sidor, COUNT(rev_page) AS antal_revisioner, MIN(rev_timestamp) AS första, MAX(rev_timestamp) AS senaste FROM revision 
INNER JOIN categorylinks ON rev_page=cl_from WHERE
cl_to LIKE 'Robotskapade_%artiklar' AND cl_type='page'
GROUP BY cl_to
ORDER BY första
*/

/*
SET sql_mode='PIPES_AS_CONCAT';
select '- [['||pl_title||']] <!--'|| c ||' Links-->' as output
from (select pl_title , count(*) as c , pl_namespace
      from pagelinks 
      where pl_from_namespace=0 and pl_namespace=0
      group by pl_title, pl_namespace
      having count(*)>100) as pl
  left join page
    on (pl_title=page_title and page_namespace= pl_namespace)
where page_id is null
order by c desc
*/

/*
SELECT pl_namespace, pl_title FROM pagelinks INNER JOIN page ON page_id=pl_from INNER JOIN categorylinks ON page_id=cl_from
WHERE pl_from_namespace = 4 AND page_title='Utmärkta_artiklar' AND cl_to='Utmärkta_artiklar'
*/

/*
SELECT antallangs, COUNT(*) AS antal FROM {
SELECT COUNT(*) AS antallangs FROM langlinks INNER JOIN categorylinks ON ll_from=cl_from INNER JOIN page on cl_from=page_id
WHERE page_namespace = 0 AND cl_to = 'Robotskapade_Islandartiklar'
GROUP by ll_from
}
GROUP BY antallangs
ORDER BY antal 
LIMIT 50
*/


/*
SELECT rc_user_text AS Användare, COUNT(rc_id) AS Redigeringar, MAX(rc_timestamp) AS Senaste  FROM recentchanges
WHERE rc_bot=0 AND rc_type < 5 
GROUP BY rc_user_text
HAVING Redigeringar > 19
ORDER BY Redigeringar DESC;
*/
/*
SELECT user_name, user_editcount from user
ORDER BY user_editcount DESC
LIMIT 100;
*/


/*SELECT el_from, el_to,  substring(el_to,20,999) AS what, INSTR(substring(el_to,21,999),'/') as pos, substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) as del FROM externallinks
*/
/*
SELECT el_from, page_title, substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) AS del FROM externallinks
INNER JOIN page ON el_from=page_id
WHERE el_to LIKE 'http://runeberg.org%' AND /* page_namespace=0 AND 
NOT substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) IN ('authors','adelskal','anrep','biblblad','bioprast','bioprof','biosvman','blaabog','blanchesam','bokobibl','bortgang','brehm','bwgbgteat','danforf','dbl','dialektl','dimh','eckmat','ekbohrn','ensv1948','ensvtekn','eurkonst','famijour','fataburen','fattig25','finminn','gamlagot','gbgpg','haandlex','hagberg/l','hallhist','halvforf','hgsl','histbib','hvar8dag','hvemerhvem','hwsvkvin','ink','jekforf','kalmherd','ksvperson','kxvtid','kulbild','kyrkohist','landostad','lantuppsl','linnstrom','lio1962p','lukat','mbsvlitt','medhist','','narkyrk','nf','nfaa','nfab','nfac','nfad','nfae','nfaf','nfag','nfah','nfai','nfaj','nfak','nfal','nfam','nfan','nfao','nfap','nfaq','nfar','nfas','nfat','nfba','nfbb','nfbc','nfbd','nfbe','nfbf','nfbg','nfbh','nfbi','nfbj','nfbk','nfbl','nfbm','nfbn','nfbo','nfbp','nfbq','nfbr','nfbs','nfbt','nfca','nfcb','nfcc','nfcd','nfce','nfcf','nfcg','nfch','nfci','nfcj','nfck','nfcl','nfcm','nfcn','nfco','nfcp','nfcq','nfcr','nfm','nordflor','norkal','nyaord','oftl','ordochbild','pedagtid','pgnarke','pk','pgosterg','pht','portr427','religrys','rikskal','rosenberg','rostrad','salmonsen','samlaren','sbh','sfl','sjhungryss','skarahal','skarahe1','sonkal','spg','samtid','sqvinnor','statskal','steelswe','stf','strokirk','svcohrs','svda','svea','sveapres','svebiblio','svekon19','svekonst80','sverhist','svetym','svhistfry','svindkal','svkyrhis','svlartid','svlihist','svsf','tektid','thamaros','thamoreb','thamstoc','thamupps','uppfinn','uppsalla','vemardet','vemarhon','vemarvem','vemindu','vemochvad','vemvardet','wiesminn')
GROUP BY del
ORDER BY del DESC
*/

/*
SELECT substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) AS del, COUNT(*) AS antal FROM externallinks
WHERE el_to LIKE 'http://runeberg.org%' AND page_namespace=0 AND
NOT substring(el_to,21,INSTR(substring(el_to,21,999),'/')-1) IN ('authors','adelskal','anrep','biblblad','bioprast','bioprof','biosvman','blaabog','blanchesam','bokobibl','bortgang','brehm','bwgbgteat','danforf','dbl','dialektl','dimh','eckmat','ekbohrn','ensv1948','ensvtekn','eurkonst','famijour','fataburen','fattig25','finminn','gamlagot','gbgpg','haandlex','hagberg/l','hallhist','halvforf','hgsl','histbib','hvar8dag','hvemerhvem','hwsvkvin','ink','jekforf','kalmherd','ksvperson','kxvtid','kulbild','kyrkohist','landostad','lantuppsl','linnstrom','lio1962p','lukat','mbsvlitt','medhist','','narkyrk','nf','nfaa','nfab','nfac','nfad','nfae','nfaf','nfag','nfah','nfai','nfaj','nfak','nfal','nfam','nfan','nfao','nfap','nfaq','nfar','nfas','nfat','nfba','nfbb','nfbc','nfbd','nfbe','nfbf','nfbg','nfbh','nfbi','nfbj','nfbk','nfbl','nfbm','nfbn','nfbo','nfbp','nfbq','nfbr','nfbs','nfbt','nfca','nfcb','nfcc','nfcd','nfce','nfcf','nfcg','nfch','nfci','nfcj','nfck','nfcl','nfcm','nfcn','nfco','nfcp','nfcq','nfcr','nfm','nordflor','norkal','nyaord','oftl','ordochbild','pedagtid','pgnarke','pk','pgosterg','pht','portr427','religrys','rikskal','rosenberg','rostrad','salmonsen','samlaren','sbh','sfl','sjhungryss','skarahal','skarahe1','sonkal','spg','samtid','sqvinnor','statskal','steelswe','stf','strokirk','svcohrs','svda','svea','sveapres','svebiblio','svekon19','svekonst80','sverhist','svetym','svhistfry','svindkal','svkyrhis','svlartid','svlihist','svsf','tektid','thamaros','thamoreb','thamstoc','thamupps','uppfinn','uppsalla','vemardet','vemarhon','vemarvem','vemindu','vemochvad','vemvardet','wiesminn')
GROUP BY del
ORDER BY antal DESC
*/

/*
DESCRIBE geo_tags;

SELECT gt_lat, gt_lon, gt_primary, rc_title, rc_timestamp, rc_type FROM recentchanges
INNER JOIN geo_tags ON gt_page_id = rc_cur_id
WHERE rc_user_text='Larske' AND rc_type<5 AND gt_primary=1 AND gt_lat>55 AND gt_lat<69.5 AND gt_lon>10 AND gt_lon<25
ORDER BY rc_timestamp ASC
*/

/*
SELECT COUNT(*) FROM templatelinks;
*/

/*
SELECT NOW(), rc_timestamp FROM recentchanges
ORDER BY rc_timestamp DESC
LIMIT 10
*/

/*
SELECT MID(rc_timestamp,1,10) AS timme, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170728000000' AND rc_timestamp < '20170730060000' AND rc_bot LIKE '0' AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY timme
ORDER BY timme 
LIMIT 1000;


USE trwiki_p;
SELECT MID(rc_timestamp,1,10) AS timme, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170702800000' AND rc_timestamp < '20170730060000' AND rc_bot LIKE '0'  AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY timme
ORDER BY timme 
LIMIT 1000
*/

/*
SELECT COUNT(*)-58 FROM recentchanges 
WHERE rc_user_text = 'LarskeBot' AND rc_timestamp > '20170614110700'
LIMIT 50
*/

/*
SELECT pl_title FROM pagelinks INNER JOIN page on pl_title=page_title
WHERE pl_from = page_id AND pl_from_namespace = pl_namespace
LIMIT 1000
*/

/*
USE svwiki_p;
SELECT MID(rc_timestamp,1,8) AS dygn, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170422000000' AND rc_timestamp < '2017050600000' AND rc_bot LIKE '0' AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY dygn
ORDER BY dygn 
LIMIT 1000;

USE trwiki_p;
SELECT MID(rc_timestamp,1,8) AS dygn, COUNT(DISTINCT rc_user_text)
FROM recentchanges
WHERE rc_namespace LIKE '0' AND rc_timestamp > '20170422000000' AND rc_timestamp < '20170506000000' AND rc_bot LIKE '0' AND (rc_type LIKE '0' OR rc_type LIKE '1')
GROUP BY dygn
ORDER BY dygn 
LIMIT 1000
*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
