Bliss. Am home.
import turtle s = turtle.getscreen() t = turtle.Turtle() turtle.title("Happy Valentine's Day!") turtle.bgcolor("red") t.pencolor("white") t.fillcolor("white") t.up() t.goto(-40,0) t.down() t.dot(100) t.up() t.goto(40,0) t.down() t.dot(100) t.up() t.goto(80,-30) t.down() t.begin_fill() for i in range(3): t.right(120) t.fd(160) t.end_fill() t.color('white') t.up() t.goto(0,150) t.down() style = ('Courier', 30, 'italic') t.write('I love you!', font=style, align='center') t.hideturtle() input("I love you!")
Say you have a table in your DB and and the way it’s set up is as a static “current state” rather than as a timestream. The following is example data from a table called
towns has an id, a name, an integer (0 or 1) representing blocked status and a planning stage string.
In this kind of setup, when a status parameter changes, it changes forever and the previous status is lost.
Here’s how you can make a daily status record so you can look back and see the history of an item’s status.
1. Create a stored function to figure out the status
If status is very obvious, you may not need this step. However, as data gets more complicated, you may find that status isn’t straight-forward. For the
towns table above, the following conditions apply to work out the status:
- The town can be blocked or not blocked.
- The possible states for
planning_stageare “pending”, “audited” or “complete”
The possible statuses follow (these examples are all made up so don’t worry too much about it making sense)
The stored function is going to take the
town_id and return a string with the status. We’re also going to add a status “UNKNOWN STATUS” to catch cases that don’t fit the requirements and a “NO SUCH TOWN” status in case the
town_id passed in is bogus.
drop function if exists get_town_status; DELIMITER // CREATE FUNCTION `get_town_status`(input_town_id int(10)) RETURNS varchar(31) CHARSET utf8 DETERMINISTIC BEGIN DECLARE town_status varchar(31) charset utf8; DECLARE town_exists, is_blocked, is_pending, is_audited, is_complete int; select town_id, blocked, if(planning_stage="pending",1,0), if(planning_stage="audited",1,0), if(planning_stage="complete",1,0) into town_exists, is_blocked, is_pending, is_audited, is_complete from towns where town_id=input_town_id; set town_status = case when town_exists is null then "NO SUCH TOWN" when is_blocked=1 and is_pending=1 then "Blocked pending approval" when is_blocked=0 and is_pending=1 then "Ready for audit" when is_blocked=1 and is_audited=1 then "Blocked during audit" when is_blocked=0 and is_audited=1 then "Ready for certification" when is_blocked=1 and is_complete=1 then "Refused" when is_blocked=0 and is_complete=1 then "Certified" else "UNKNOWN STATUS" end; RETURN town_status; END // DELIMITER ;
We can then use it like
select get_town_status(123456789), get_town_status(36972), get_town_status(63406);
Which returns “NO SUCH TOWN”, “Blocked pending approval”, “Ready for certification”
2. Create a status record table
CREATE TABLE town_status ( town_id int(10) unsigned NOT NULL, town_status varchar(31) CHARACTER SET utf8 DEFAULT NULL, recorded_at date NOT NULL, PRIMARY KEY (town_id,recorded_at) ) ;
This sets up a link between the town and the status on a particular date. The primary key is a combination of the
town_id and the
recorded_at date, meaning you can only have one record for each town on any given day.
You’ll note that although
town_id is an id from a foreign table, it doesn’t have a foreign key constraint. This is because even if the town is deleted, you will still want to enter an historic record of its final stage.
The initial population of the table will simply add today’s values for all towns to the status table.
insert into town_status (select id, get_town_status(id), curdate() from towns);
3. CREATE A current status view
The current view will show the latest recorded status for each town. This view will self-join the table
town_status finding the
latest_date for each town.
This view will be useful if you want to quickly get the current recorded status of all your towns (for example if calculating it using
get_town_status is spendy).
create view current_town_status as ( select ts.* from town_status ts join ( select town_id, max(recorded_at) as latest_date from town_status group by town_id) x on x.town_id = ts.town_id and x.latest_date = ts.recorded_at ) ;
4. make a daily event to store the status each day
We’ll make an event to recur at 00:15 every day, starting tonight. This event will record the status for each town if it has changed since the most recently stored value. Two caveats apply: 1) we’ll need to add a new row if the town is a new entry into the
towns table and 2) we’ll also need to record if a town disappears from the
The left join and null check in the first insert statement will take care of new towns: if the town is not already on the
town_status table, it will be added with its status.
To manage the second case, where the town has been deleted from the
towns table, we check to see if there are any towns on the
town_status table but not on the
towns table, where the last recorded status was not “NO SUCH TOWN”. If any examples are found, then add a “NO SUCH TOWN” status for this
town_id for today.
#Nightly event to keep track of town status changes drop event if exists record_town_status; delimiter | create event record_town_status on schedule every 1 day STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 15 MINUTE) do begin insert ignore into town_status ( select t.id, get_town_status(t.id), curdate() from towns t left join current_town_status ts on t.id=ts.town_id where (ts.town_status is null or get_town_status(t.id) <> ts.town_status) ) ; insert ignore into town_status ( select ts.town_id, "NO SUCH TOWN", curdate() from current_town_status ts left join towns t on t.id = ts.town_id where t.id is null and ts.town_status <> "NO SUCH TOWN" ) ; END | delimiter ;
Over time, the
town_status table will build and you will be able to see a history of events. E.g.
select * from town_status where town_id=43624;
Might return something like
43624 Blocked pending approval 2020-06-01
43624 Ready for audit 2020-06-30
43624 Ready for certification 2020-07-01
43624 Certified 2020-07-06
And then you’ll be able to things like analyse how long it typically takes to move from stage to stage.
The same principles apply for statuses that need to be recorded more or less frequently, you would just need to adjust the time unit in the status table and the event timing. Beware unchecked growth where statuses change frequently and your initial data table is very large.
(FYI: My DB is MySQL 5.7 so doesn’t support window functions)
I am within 3 weeks of first meeting Google Data Studio (DS). I am head of data at a small firm that was subsumed into a larger firm last year. Now that the initial merge issues are clearing, we’re getting down to the brass tacks of making our very different technologies work together. They’re very google-oriented – lots of BigQuery & Go and the data bods use R. I’m more MySQL and Python myself. We didn’t have a regular BI tool, hence why I’m getting up to speed with DS atm. DS gives a lot of bang for your buck (it’s free!), but has some interesting quirks.
I’ve had a few other battles with DS already, hopefully I’ll get to expand on those in further articles. Some of these are real head-scratchers; things you would think are such obvious requirements that the lack of them certainly makes me think my dearth of experience is the issue. Such a one seems to be the inability to filter values by time. An initial internet search turned up a couple of hits, but nothing really concrete:
- This StackOverflow question from 7 months ago
- And this data studio community question from 18 months ago
So! How I can filter a set of timestamped results to show only values from the past hour?
I’ve inserted a Chart > Scorecard and I want to sum up the values in column 2 if the time in column 1 falls within the past hour.
DS has grand tools if I want to filter by the date:
But it seems only to let you set the date, the time of day is not adjustable. Minimum “Unit” granularity is “day”; even when my data type is
Date & Time or
Date Hour Minute.
Okay, so now what? I’ll try to make a different kind of filter – based on the time value
So, it starts out looking hopeful, I can choose the time metric and only include times that are later than a date and time I pick! But on closer inspection, that’s a fixed time, not a relative one, so no dice. Clicking around, nothing else in the filter allows relative comparison, so I’ll have to think of something else.
Hmmm. What about if I set an
is_in_past_hour boolean? I’ll make a new calculated field. Oh, and by the way, DS doesn’t do IF statements. It’s
case or nuffink.
After a bit of jiggery-pokery, including
case when datetime_diff(current_datetime(),the_time,HOUR)=0 then true else false end
erroring with Invalid formula – Invalid input expression. – WHEN conditions must compare a dimension or metric with a literal value.
I ended up ditching the case statement altogether and made an
hours_before_now variable instead. I will filter results so that only results where this variable is 0 are included.
Phew. All done. But hang on, why are the results still not matching what I’m expecting?
It’s rounding half hours up or down.
So I switch all the references in my filters and variables from hours to minutes and check there are fewer than 60 of them.
Finally, this works. I’m getting the same value in the DS report as this simple wee query in MySQL:
select sum(my_value) from my_table where the_time >= date_add(now(), interval -1 hour) ;
Time (out) for a coffee.
Blah blah, been away, insert excuse here.
I’ve had a fab Christmas, I hope you have too. I returned to full-time work recently and have been too wiped to enjoy my fam let alone anything else. But over crimbo I finished a pair of socks – no languishing on second sock for me, just whip, whip, go.
They’re patterned, and done in variegated, so of course the patten is indecipherable. They’re “To the ocean” by Helena Callum from knit now 60. I only made one mod- to slip the travelling stitch every other row.
My bro got me knitting stuff from Winnie’s craft cafe for Christmas, including this gorgeous yarn bowl by carol burke. I <3 it muchly.
I’m def in a sock mood and lil bro hit the nail o the head with his gift cos the other things were sock yarn and a sock book. I’m thinking of joining the 12 socks in 2017 challenge set out by Dee Murphy in the DKC knit along group on facebook. Just to be clear, I’m taking that as 12 individual socks, not 12 pairs as srsly? Wrists aren’t cut out for that amount of action!!
Have started a wee leftover mitred sq blanket tho.
What else? Hmmm my resolutions are the same as always and I expect they’ll all do what they usually do and fizzle out by next week. Except one. Apols for the cryptic stuff that follows. I’m doing something that’s making me unhappy ATM and I’m working on digging my way out of that. My resolution for January is to study up for the first, best opportunity to get out. Happens in Feb. Fingers crossed.
Honeycomb’s kinda my thing, I make it and bring it to shindigs, or pop it in a takeaway box if I want to make a quick gift. It’s very easy, except for one thing, which is catching it at the right moment – after the sugar melts but before it burns. If the sugar is even an eensy teensy bit caught, the honeycomb will go right over, because as it foams upon addition of the bicarb, it gets even hotter inside, or traps the heat or something.
I was at a wonderful studio warming party yesterday hosted by Renee Callahan (East London Knit). I brought some honeycomb and exclaimed how easy it was to make but failed to give any details. Hey, it wasn’t the only bubbly stuff consumed that afternoon, ‘k?
So my apologies for teasing, and here it is:
200g white caster sugar (brown sugar won’t work)
50g golden syrup (measured by weight straight into the saucepan on a scales – spoons are kinda pointless with golden syrup)
2 teaspoons bicarbonate of soda
Important: silicon bakeware to put it in (it’ll just stick to tins and glass) do not butter or otherwise prepare
1) melt the sugar and syrup together over a medium to hot heat and until it all starts to bubble briskly – I don’t think you’re supposed to stir but I do anyway
2) turn the heat down so it’s still bubbling, but more slowly. Let it completely go to liquid, it should be a lovely clear golden colour by now.
3) take it off the heat, rapidly stir in the bicarb, and get that stuff into the silicon bakeware as soon as possible
4) let it cool completely. Don’t prod or poke at it. Don’t even pick bits off the side. Go lick the spoon instead. (Seriously, it could collapse)
5) try to break it into chunks without touching it. Baking parchment rocks, plastic bags are bad (sticky!!!!)
6) eat same day or maybe day after, and don’t get it wet or feed it after midnight.
:) xxxxx e enjoy!
I was very flattered to be interviewed for the Blasta podcast. Dearbhla asked some great questions and got me thinking about the design process.
During their chat they speak about a number of patterns:
- The sweater Elanor is wearing is her Eleven Hundred Dollars pattern from “Hitch: Patterns Inspired by the Films of Alfred Hitchcock” published by Co-operative Press.
- Her first published pattern, In the Loop, was her response to a designer challenge from Knit Now magazine.
- At Edinburgh Yarn Festival Elanor was wearing her loom band hat.
- Patterns that Dearbhla has tech-edited for Elanor are Sinead’s Hotty Botty and KISS Mitts.
- Last, but not least, Elanor’s pattern Lady Lismore was one of the Knitty surprise patterns for their Spring + Summer 2015 issue.
To celebrate her Knitty success, Elanor has set up a KAL. The cast-on date was May 1st but you have until July 20th…
View original post 247 more words
My beautiful sister models the crescent-shaped shawlette in St. Pancras station.
Lady Lismore features a trio of wrapped-stitch stitch patterns. Wrapping a stitch is a way to make a longer-than-usual stitch. The more times you wrap the stitch as you make it, the longer it will be. On the next row, you drop the extra wraps and just work with the elongated stitch. In Lady Lismore, different ways of manipulating the wrapped stitches give different effects. Each band of wrapped stitches is separated by some gratifyingly easy stocking stitch (stockinette) sections. Increases are at the edges only, and are consistent.
The Large sample for Knitty was worked using a variegated purpley-red and an almost-completely-solid grellow.
Dragonfly Fibers Pixie 4-ply [100% Superwash Merino; 475m/4oz skeins]
[MC] Redbud; 1 skein
[CC] Weaverknits Grellow; 1 skein
The above Large shawl used up less than 100g (422yds/386m) of MC and 61g (256yds/234m) of CC.
I originally worked up the Small shawl in electric blue and shocking pink.
West Yorkshire Spinners Signature 4-ply [40% Wool, 35% Blue Faced Leicester, 25% Nylon; 400m/100g balls]
[MC] Sarsaparilla; 1 ball
[CC] Bubblegum; 1 ball
In fact the Small shawl used about 80g (350yds/320m) of MC and 40g (175yds/160m) of CC.
Now for the KAL info
Start Date: May 1st – this should give everyone enough time to source yarn and materials
End Date: July 20th – this will give me enough time to wrap up the competition before my kids go on school holidays!
How to enter: Sign up by introducing yourself and showing or describing the yarn you’d like to use
Prizes: To be announced, but categories will include
Most “loved” – as voted by people reading the FINISHED!!! thread (automatically entered by signing up below)
Most “helpful” – as voted by clicking the “helpful” button on the project pages (automatically entered by signing up below)
First to finish – no cheating! If you’re entering this category, we need to see a pic of your unused yarn next to a date (on or after May 1st) in a newspaper or in a bank or similar (entry by posting date pic on your project page and signing up below, first to post a project with pics in FINISHED!!! thread wins)
Best riff – I just love it when people take an idea of mine and really make it their own, whether by changing up the colours, changing the shape, adding beads, shells, whatever – I will pick a handful and then ask for votes (entry by signing up below and stating your intention to riff)
If this sounds like your kind of -along, then please step right this way and sign up here! I’m so excited to see what you do!
Please ask questions if you have them, pop ’em into a comment below.
These are some of the dappled gradient cakes that will be available on Shamu makes’ stall in the pop-up market at EdinYarnFest.
Come take a look at all the squishable treats this weekend. Looks like it’s gonna be amazing. Tix £8 on the door at the corn exchange. Drop by and say hi! Xxx e