I am guessing it was just a bit too silly then.
OK, how about this for a suggestion for a new cross-denomination, cross project/sub-project MM version of a a SuperDuperBok, that might appeal, due to it being slightly ridiculous, and nearly impossible to get, and therefore potentially very mysterious!
Here goes. For every value-based MM, you get one of these when you hit the target on the nose. So for every Project and Subproject, if I were to have actually achieved a score of exactly 1 Million (or 1K/10K/25K/50K etc.), then I would get one of these. Obviously there has to be a level of tolerance to accommodate projects that give out decimal cobblestones, just like of for Trigggls/Boks etc. but it has to be brutal - it is +/- 0.5.
Not sure how easy it would be to code for but I just thought I would throw it into the pot. I would imagine that at the same time you are accumulating all the value related MM’s you could stick a plus one into a new counter for each one that is bang on.
In case you go for it, I have thought up some suggestions for a name for this new MM, and offer up the following selection. Nebbers, Conks or Brightys - my favourite is Nebber.
I openly confess that I only thought of this when I realised that I had achieved a second one of them today, without actually trying to. I would currently get one for the Primaboinca project where I have exactly 1M, and one for SRBase’s ‘Sierpinski Base’ Sub-project where I have – probably fleetingly - exactly 250K.
It’s actually quite easy to check if you have any as a quick scan down the Score column on CPIUD page and the SubProject page looking for the exact MM target numbers.
For something like this it would be nonsense to have a limit of more than one before it showed, as I suspect that very few people will have any at all, never mind more than one – I would not be remotely surprised to find that I was in the number one spot with two.
So, mysterious and hard to get. Are you not a wee bit tempted? :-)
I am guessing it was just a bit too silly then.
no, it has merit, just not got round to it...
That's good, but having said that I have had a bit of a thought about this and I think the +/- 0.5 stones is probably too tight, and possibly too difficult to get! There is no point in having a mysterious one if there is nobody to notice the mystery! My instinct tells me that there ought to be some small sensible tolerance - the same across all MM targets to keep things simple - and I think it probably ought to be either +/- 10 stones, or perhaps 0.1% of the relevant MM (i.e. plus OR minus 0.1%) if that is easy to do.
I feel that this way people would see a few more of them and wonder how they got something for reaching 999,001!!! If anything it would probably add to the mystique. What do you think?
Just thought I'd post something to this again so that it stays alive. I still like this idea.
So this would need to check
for each project
(have I got exactly 10k or 25K or 50K or 100K or 500K or 1M etc
Whilst doable, that's a lot of calculations. But i will try and see
This might work ?
Code:MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user where truncate(metric1,0) in (10000,100000,500000,1000000) group by cpid order by mycount desc limit 0,10; +----------------------------------+------------+---------+ | cpid | nick | mycount | +----------------------------------+------------+---------+ | 0dd9749ec16c8900f065d871290156bc | BAR | 7 | | 2286c5351f0f2d2f5d7fee45967f22b8 | Baryna.ch | 5 | | e33013c782482e80caf0c65b476a2d87 | JayPi | 4 | | 89e772f65716bba7c962b4a13d14e350 | AMDave | 4 | | d7a3d9afaf379c6984d47f1d9672f5e2 | UL1 | 3 | | 8f15950e07e24a5c0c73016a77c952e9 | Nflight | 3 | | 61c4eaaca6667bc9dda3412188c5f173 | x3mEn | 3 | | 16b5360510c4a408ca02d2ff07ab4a9e | RAD-Poland | 2 | | 8496797f94775aac42635504add88c62 | $ | 2 | | 2e8203c09bffa7dd415284af2124eb53 | skivelitis | 2 | +----------------------------------+------------+---------+ 10 rows in set (8.956 sec)
Not so keen on this one, Coleslaw's https://www.free-dc.org/showthread.php?57942-Coleslaws however is a winner
Sorry to interrupt, but can we please stay on track here, this is about Nebbers!
Excuse my lack of SQL but is this just a trial? I was thinking more ALL of the credit-value based MMs so 1000 10000, 25000 etc and for both Projects and SubProjects and with a tolerance of somewhere about 0.1% either way. It may seem like a lot to do but I feel there will not be many of them so perhaps it won't take too long, though if it does, then by all means drop the idea. Cheers
This was just to show how it could be done.
FWIW, doesn't seem to be much different when I do all of them
Code:MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user where truncate(metric1,0) in (1000,10000,25000,50000,100000,250000,500000,750000,1000000,2500000,5000000,10000000,25000000,50000000,100000000,250000000,500000000,1000000000) group by cpid order by mycount desc limit 0,10; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 170374 Current database: static +----------------------------------+-----------+---------+ | cpid | nick | mycount | +----------------------------------+-----------+---------+ | 0dd9749ec16c8900f065d871290156bc | BAR | 7 | | 2286c5351f0f2d2f5d7fee45967f22b8 | Baryna.ch | 5 | | 8f15950e07e24a5c0c73016a77c952e9 | Nflight | 4 | | e33013c782482e80caf0c65b476a2d87 | JayPi | 4 | | 89e772f65716bba7c962b4a13d14e350 | AMDave | 4 | | 61c4eaaca6667bc9dda3412188c5f173 | x3mEn | 3 | | 476d777382a3555be83f6af9ab59c9f9 | Szopler | 3 | | 8496797f94775aac42635504add88c62 | $ | 3 | | d7a3d9afaf379c6984d47f1d9672f5e2 | UL1 | 3 | | 99fecfdc6caaa3ee9f7365297dd65634 | williami | 3 | +----------------------------------+-----------+---------+ 10 rows in set (10.022 sec)
I think I see, and that looks like a promising start - other than the "MySQL server has gone away" bit!
Combine it with SubProject breakpoints and Nebbers is go - assuming there's some tolerance in there somewhere - though I don't see it if it is. No I'm not looking for a free SQL lesson! ;-) Good work I think!
The nice thing about this one is that I think everyone is as likely as anyone else to get one. OK so the more projects / SubProjects you have worked on increases the odds a fair bit, but someone could have worked on 5 projects and have one or two of these and quite probably wont know why! I always got the impression that you were quite keen on mysterious ones Bok.
I do think the 0.1% tolerance would make a difference if you haven't already got it in there. I would have thought that they will be far more commonplace with that tweak. Or would it be easier to just arbitrarily say plus or minus 500 say, or some such? I'm not bothered, but I suspect you will find more of them with percentages. Why not have a look at the data and pick what you think works best.
It would make it much more difficult to make it to have a range of values (tolerance) as in sql terms that would be individual between (9999 and 10001) for example
Though I certainly see the appeal of doing that.
but, saying that - it would only run once per day.Code:MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user where (truncate(metric1,0) between 9999 and 10001) or (truncate(metric1,0) between 99999 and 100001) group by cpid order by mycount desc limit 0,10; +----------------------------------+-------------+---------+ | cpid | nick | mycount | +----------------------------------+-------------+---------+ | 0dd9749ec16c8900f065d871290156bc | BAR | 8 | | 2286c5351f0f2d2f5d7fee45967f22b8 | Baryna.ch | 7 | | 61c4eaaca6667bc9dda3412188c5f173 | x3mEn | 6 | | e33013c782482e80caf0c65b476a2d87 | JayPi | 5 | | a848091602d6dade8ee661252d106d2d | rilian | 4 | | 89e772f65716bba7c962b4a13d14e350 | AMDave | 4 | | d5f358d1a7913ab61d7ea87c85aafbfd | Buster Gunn | 4 | | 8f15950e07e24a5c0c73016a77c952e9 | Nflight | 3 | | 3a045e428e45f087f7cc832bdb4791ff | RAD-Poland | 3 | | 2e8203c09bffa7dd415284af2124eb53 | skivelitis | 2 | +----------------------------------+-------------+---------+ 10 rows in set (9.725 sec)
I feel that it wouldn't really be worth doing if there were going to be so few of them - so I wouldn't bother unless tolerances are doable. Just had a quick look at BAR and it appears that whoever he or she is, ramped up a bunch of Trigggls and stopped and may not have Boinced for some time so this could be wasted on them!!! (On that note, would it be easy to introduce a Date Last Seen under the current date First Seen in the headers - perhaps you already track this but don't show it? Obviously you do track the hosts but that would be a much more long winded approach, and you could just update a date when you update any other stuff in the daily update. When displaying you could just show "Unknown" if the date is blank - Sorry - I digress)
In your example above I would argue that it should have been between 99990 and 100010 for the second one, though after seeing it 0.5% (maybe more?) might be more interesting, as more people would get them and quite possibly have no clue as to why. Why anyone think they would get 'rewarded' for a subproject reaching 99952, 499751 or 24876 credits after all? That would be more interesting. Maybe you should delete this thread once you have decided ;-)
Just in case it helps you Bok, I knocked this up - it doesn't take into account SubProjects but I suspect a bit of copy and paste would do so in a few seconds. I went for a 0.5% tolerance. Why not see how long it takes to run and decide if it's doable or not
MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user
where
(truncate(metric1,0) between 9995 and 10005)
or (truncate(metric1,0) between 24975 and 25025)
or (truncate(metric1,0) between 49950 and 50050)
or (truncate(metric1,0) between 99995 and 10050)
or (truncate(metric1,0) between 249750 and 250250)
or (truncate(metric1,0) between 499500 and 500500)
or (truncate(metric1,0) between 749250 and 750750)
or (truncate(metric1,0) between 999995 and 1000050)
or (truncate(metric1,0) between 2497500 and 2502500)
or (truncate(metric1,0) between 4995000 and 5005000)
or (truncate(metric1,0) between 9999995 and 10000050)
or (truncate(metric1,0) between 24975000 and 25025000)
or (truncate(metric1,0) between 49950000 and 50050000)
or (truncate(metric1,0) between 99999995 and 100000050)
or (truncate(metric1,0) between 249750000 and 250250000)
or (truncate(metric1,0) between 499500000 and 500500000)
or (truncate(metric1,0) between 999999995 and 1000000050)
or (truncate(metric1,0) between 2497500000 and 2502500000)
or (truncate(metric1,0) between 4995000000 and 5005000000)
or (truncate(metric1,0) between 9999999995 and 10000000050)
or (truncate(metric1,0) between 24975000000 and 25025000000)
or (truncate(metric1,0) between 49950000000 and 50050000000)
or (truncate(metric1,0) between 99999999995 and 100000000500)
or (truncate(metric1,0) between 249750000000 and 250250000000)
or (truncate(metric1,0) between 499500000000 and 500500000000)
or (truncate(metric1,0) between 999999999995 and 1000000000050)
or (truncate(metric1,0) between 2497500000000 and 2502500000000)
or (truncate(metric1,0) between 4995000000000 and 5005000000000)
or (truncate(metric1,0) between 9999999999995 and 10000000000050)
group by cpid order by mycount desc limit 0,10;
subprojects are a different table so it that complicates..
I'm not sure you have them all correct, for instance 99995 and 10050, in fact all of your ones like that have a 5 tolerance on the prior but much more on the post side
Also don't really need the truncate part when we are doing between.
This one though gives this subset
Code:+----------------------------------+---------------+---------+ | cpid | nick | mycount | +----------------------------------+---------------+---------+ | e33013c782482e80caf0c65b476a2d87 | JayPi | 24 | | 476d777382a3555be83f6af9ab59c9f9 | Szopler | 22 | | 61c4eaaca6667bc9dda3412188c5f173 | x3mEn | 18 | | 0dd9749ec16c8900f065d871290156bc | BAR | 18 | | 5dad1593a3bd6cd31ce4a9b507ba20fc | Ananas | 12 | | 3bfe3268986a7fe9ebaa5acc02e69393 | somanyroads | 9 | | 7907e81b12e62de3e9a0c5459a973318 | Mark Gallaher | 9 | | 5ce7cfc3ff8b21f50b0eb24a7d29ffcd | FourOh | 9 | | d7a3d9afaf379c6984d47f1d9672f5e2 | UL1 | 8 | | 8e0b099acd93adc95684151a4eef9b86 | Bruce Kennedy | 7 | +----------------------------------+---------------+---------+ 10 rows in set (21.496 sec)
Ah yes, sorry - I had a rush of blood just after 1am and started doing that. I was tired and started copying and pasting and just adding zeros. Never a good time for my best work - apologies (I guess I lose the chance of being a subcontractor! ;-), not that I have touched SQL for nearly 30 years anyway
So, whilst I think we could expect a good few more entries if it were corrected and if Subcontracts were added, Nebbers looks like being a fun enough one to me. What do you think?
I'll correct my mistakes and return it later today.
And what were you doing checking it out at 3:49am or is that my time?
Apologies again for my stupid errors in that, that is probably the first time in all my years of programming that I didn't know how to calculate the values at runtime from an array of fixed values and a parameterised percentage tolerance! Bring back Cobol! Hey ho, it was fun but this ought to be a bit better, hopefully even right.
MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user
where
metric1 between 995 and 1005
or metric1 between 9950 and 10050
or metric1 between 24875 and 25125
or metric1 between 49750 and 50250
or metric1 between 99500 and 100500
or metric1 between 248750 and 251250
or metric1 between 497500 and 502500
or metric1 between 746250 and 753750
or metric1 between 995000 and 1005000
or metric1 between 2487500 and 2512500
or metric1 between 4975000 and 5025000
or metric1 between 9950000 and 10050000
or metric1 between 24987500 and 25012500
or metric1 between 49950000 and 50050000
or metric1 between 99950000 and 100050000
or metric1 between 249875000 and 250125000
or metric1 between 499500000 and 500500000
or metric1 between 999500000 and 1000500000
or metric1 between 2498750000 and 2501250000
or metric1 between 4995000000 and 5005000000
or metric1 between 9995000000 and 10005000000
or metric1 between 24987500000 and 25012500000
or metric1 between 49950000000 and 50050000000
or metric1 between 99950000000 and 100050000000
group by cpid order by mycount desc limit 0,10;
I assume you can just copy and paste and change the names for SubProjects but I don't know.
I do think it's a good idea yes, just have to figure out the logistics of combining the subproject data also (probably just have a table with both columns calculated separately then add rather than using a union).
Code:+----------------------------------+--------------+---------+ | cpid | nick | mycount | +----------------------------------+--------------+---------+ | 3a045e428e45f087f7cc832bdb4791ff | RAD-Poland | 84 | | e33013c782482e80caf0c65b476a2d87 | JayPi | 73 | | 89e772f65716bba7c962b4a13d14e350 | AMDave | 58 | | 61c4eaaca6667bc9dda3412188c5f173 | x3mEn | 49 | | 0dd9749ec16c8900f065d871290156bc | BAR | 48 | | 2286c5351f0f2d2f5d7fee45967f22b8 | Baryna.ch | 47 | | 476d777382a3555be83f6af9ab59c9f9 | Szopler | 42 | | 5e450cd04783a7e0229107be75832fd0 | Dandelion | 41 | | f337e6cc88d1503620abd08a2908ffb4 | Werinbert | 39 | | ed8ec64bc4941cd7f255ae589778e1e1 | silent Float | 38 | +----------------------------------+--------------+---------+ 10 rows in set (17.205 sec)
OK then I'll leave it in your capable hands.
Gah! I mad another mistake in it with putting zeros and 9's in the wrong places, it should be
MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user
where
metric1 between 995 and 1005
or metric1 between 9950 and 10050
or metric1 between 24875 and 25125
or metric1 between 49750 and 50250
or metric1 between 99500 and 100500
or metric1 between 248750 and 251250
or metric1 between 497500 and 502500
or metric1 between 746250 and 753750
or metric1 between 995000 and 1005000
or metric1 between 2487500 and 2512500
or metric1 between 4975000 and 5025000
or metric1 between 9950000 and 10050000
or metric1 between 24875000 and 25125000
or metric1 between 49950000 and 50050000
or metric1 between 99500000 and 100500000
or metric1 between 248750000 and 251250000
or metric1 between 499500000 and 500500000
or metric1 between 995000000 and 1005000000
or metric1 between 2487500000 and 2512500000
or metric1 between 4995000000 and 5005000000
or metric1 between 9950000000 and 10050000000
or metric1 between 24875000000 and 25125000000
or metric1 between 49950000000 and 50050000000
or metric1 between 99500000000 and 100500000000
group by cpid order by mycount desc limit 0,25;
This highlights the benefits of being able to parameterise it and work it out automatically at run time! I have no idea if SQL allows this but it would be good if it did. Anyway - sorry for messing you about, I am fairly sure it is right now.
Double ARGGGH! So I was just sorting out a test for this in a spreadsheet, and I was horrified to see that there were still some differences once I had built some formulas - which I think just goes to show how tricky it will be for people to work out why the have suddenly gotten whatever number of Nebbers they get :-) Anyway according to my spreadsheet your SQL statement should be as follows :
select cpid,nick,count(*) as mycount from boinc_user
where
metric1 between 995 and 1005
or metric1 between 9950 and 10050
or metric1 between 24875 and 25125
or metric1 between 49750 and 50250
or metric1 between 99500 and 100500
or metric1 between 248750 and 251250
or metric1 between 497500 and 502500
or metric1 between 746250 and 753750
or metric1 between 995000 and 1005000
or metric1 between 2487500 and 2512500
or metric1 between 4975000 and 5025000
or metric1 between 9950000 and 10050000
or metric1 between 24875000 and 25125000
or metric1 between 49750000 and 50250000
or metric1 between 99500000 and 100500000
or metric1 between 248750000 and 251250000
or metric1 between 497500000 and 502500000
or metric1 between 995000000 and 1005000000
or metric1 between 2487500000 and 2512500000
or metric1 between 4975000000 and 5025000000
or metric1 between 9950000000 and 10050000000
or metric1 between 24875000000 and 25125000000
or metric1 between 49750000000 and 50250000000
or metric1 between 99500000000 and 100500000000
or metric1 between 248750000000 and 251250000000
or metric1 between 497500000000 and 502500000000
or metric1 between 995000000000 and 1005000000000
group by cpid order by mycount desc limit 0,25;
I also chucked another band on the end as there are probably some people out there with trillions.
OK I will shut up for a bit now, and stop making blattantly obviuos mustakes (hopfully)
So I was testing this Nebbers idea out yesterday and I calculated on my spreadsheet how many I would get as a test, and for a laugh I added a line to the bottom of the MMs data that I import to see it's effect on my various other tables/graphs.
Well... we are just moving to the highest Tier of Covid Restrictions here this weekend so I might as well get used to filling my time!
Anyway, having been pushed into guessing my rank for Nebbers I thought that it might be amusing - if only for me - to publish my predictions just so I have something to really look forward to in Lockdown - Namely "Just how wrong could I be?"
So with that, here are the predictions from my panel of experts ;-)
MM Type Nebbers
Many 53
RankP 300
RankT 3
RankC 14
#Maxwell 80
#TeraMiRB 49
#MiRB 12
Ooh! I just can't wait!
BTW Bok , if you do ever find the time to do this one (and I'm not nagging you here), could you please put it above SubClanks and Clanks on the MMs table, as it is probably slightly less volatile than those two.
Going to try an implement this tomorrow. My wife and daughter are away for hockey this weekend, so I get some time
I kind of got it working yesterday, btu oly for the main projects. The subproject data is giving me fits due in main to cpid changes leaving orphaned data in the subproject table.
I may just implement it for main data only for now.
I suppose that would make it all the more mysterious and intriguing when you added them later!
It has just occurred to me that it could be fun to confuse Nebbers even more by adding some additional (arguably missing) ones like 2,500 5,000, 7,500, 75,000, 7,500,000, 75,000,000 etc.
It would also differentiate it a bit more from Squarepants.
I doubt many Boincers would get many of these though as I suspect most people would not stop on those, but it might give some interesting temporary rises and falls in the number of Nebbers as they 'pass through', which could keep it interesting.
Just a thought.
Have a look on stats6 and see if the data looks correct, then I'll add it to stats.
My spreadsheet predicted 27 non-SubProject Nebbers for me and that is what I have gotten so that is cool - excellent work as ever Bok.
The only alarm bell for me is that stats6 still has SubBoks and SubTeraBoks (of all things!) missing and Nebbers is appearing below Clanks. I only mention it because I don't know if you copy the whole thing or just the amended code but could you put Nebbers above Clanks and SubClanks please.
Thanks again and I look forward to you working out how to include SubProjects one of these days.
They will be above clanks after everything re-runs tonight. For now I only ran it for them and they are sharing the same unique number which sorts them.
SubBoks and subteraboks look like they are giving an error during calculation on stats6 and it's the same issue I get with Nebbers (NULL cpid on orphaned subproject data).
the newer version of Mariadb is more strict here than the mysql version on the older stats server which is why it does not appear there.
I need to solve that.
Looks like you sorted the SubBoks and SubTeraBoks out, and I see what you mean about sharing the same number now (2395) I assume you are just rejigging the lower numbers to make room. Looks good.
I assume that you have only run Nebbers for a subset of all users as I can't believe I would be so high up the running order otherwise. I also assume that the Maxwell/MiRB and TeraMiRB values will come with a full run.
Great stuff.
Stats6 looks good to me now - good work, thanks.