As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

[Programming] Mirror, mirror, on the wall, show the git diff for them all

18990929495100

Posts

  • bowenbowen How you doin'? Registered User regular
    I just want to be paid well, that's all, that's hard enough without being 3 people at once.

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • TofystedethTofystedeth Registered User regular
    edited May 2016
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.

    Tofystedeth on
    steam_sig.png
  • bowenbowen How you doin'? Registered User regular
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • TofystedethTofystedeth Registered User regular
    bowen wrote: »
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?
    What I need is more like
    select
    max(bill_activity.activity),
    bill_activity.bill_type,
    bill_activity.bill_amt,
    encounter.encounter_id
    from encounter (several joins later) JOIN bill_activity
    where bill_activity.bill_type in (blah, blah2, blah3)
    group by
    encounter.encounter_id,
    bill_activity.bill_type
    
    Basically, the most recent activity of each type, per encounter.
    But including other information (such as the activity amount) in the select, requires it to be put in the group by, which means that it then creates another line for each distinct amount when I need a single result per type x encounter.

    steam_sig.png
  • hippofanthippofant ティンク Registered User regular
    edited May 2016
    Ohhh shi, there is absolutely an SQL way to collapse that, but I forget what it was since I last had to use it some 10+ years ago.

    Edit: One way to do it is to select out the columns you want, and re-join. If you're using a GROUP BY, SQL demands that everything in the SELECT be in the GROUP BY or be inside an aggregate function, because otherwise, it doesn't know what value to return in the record. So you could take out bill_activity.bill_amt, use this as a subquery, and then join the results of the subquery with bill_amt to get the full resultset you want. I'm not sure how efficient that is though.

    hippofant on
  • urahonkyurahonky Registered User regular
    Anyone here have some experience with Go? I'm working with this project: https://godoc.org/golang.org/x/crypto/nacl/secretbox in Go and this: http://pynacl.readthedocs.io/en/latest/secret/ in Python.

    I'm generating the final token in python and sending it to this Go script.

    This is the key that was generated for me in python is:
    }\x04\x9b4H\x88\xcb>\x13\\"(\x01|\x12\x0f\xbfTj\x85I.DXOL\xba\xbd4S\x1b1
    

    How do I make this a byte array in Go? Or, at least, count the number of bytes in it?

    bytes.Count(nil, privateKey)) seems to return nothing.

    When I pass that key in using something like:
    var privateKey = []byte("}\x04\x9b4H\x88\xcb>\x13\\"(\x01|\x12\x0f\xbfTj\x85I.DXOL\xba\xbd4S\x1b1")
    

    Then:
    out, ok := secretbox.Open(nil, message, &nonce, &privateKey)
    

    I get:
    cannot use &privateKey (type *[]byte) as type *[32]byte in argument to secretbox.Open

  • urahonkyurahonky Registered User regular
    And, as usual, I figure it out within 5 seconds of hitting Submit.
    var privateKey [32]byte
    copy(privateKey[:], "}\x04\x9b4H\x88\xcb>\x13\\"(\x01|\x12\x0f\xbfTj\x85I.DXOL\xba\xbd4S\x1b1")
    

  • TofystedethTofystedeth Registered User regular
    edited May 2016
    Can anybody see anything wrong with this subquery? I'm using it in my select statement to do that lookup I was talking about earlier, and for some reason it's making Oracle complain about FROM not being where it is expected. If I comment out the query and replace with a constant (like I've done with the placeholder columns after it) it works fine, so I'm guessing I've subtly botched syntax in there but damn if I can find where.
    edit: Ahah! found the extra trailing ).

    Tofystedeth on
    steam_sig.png
  • TofystedethTofystedeth Registered User regular
    But it returns no results. Blargh.

    steam_sig.png
  • GhotiGhoti Registered User regular
    edited May 2016
    bowen wrote: »
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?
    What I need is more like
    select
    max(bill_activity.activity),
    bill_activity.bill_type,
    bill_activity.bill_amt,
    encounter.encounter_id
    from encounter (several joins later) JOIN bill_activity
    where bill_activity.bill_type in (blah, blah2, blah3)
    group by
    encounter.encounter_id,
    bill_activity.bill_type
    
    Basically, the most recent activity of each type, per encounter.
    But including other information (such as the activity amount) in the select, requires it to be put in the group by, which means that it then creates another line for each distinct amount when I need a single result per type x encounter.

    You could do a select on the MAX criteria for bill_activity within you join... assuming your unique identifier which you are joining is the encounter_id
    
    SELECT e.encounter_id, b.activity, b2.bill_activity.bill_type, b2.bill_activity.bill_amt
    FROM encounter e
    JOIN (SELECT MAX(bill_activity.activity), encounter_id FROM bill_activity GROUP BY encounter_id) b ON e.encounter_id = b.encounter_id
    JOIN bill_activity b2 ON b.activity = b2.activity
    

    At the very least, this might help get you on the right track.

    Ghoti on
  • EchoEcho ski-bap ba-dapModerator mod
    I need to get better at writing down what I mean when I look at stuff two weeks later.

    "Refactor this"

    *tries to read mind of past self*

  • TofystedethTofystedeth Registered User regular
    Ghoti wrote: »
    bowen wrote: »
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?
    What I need is more like
    select
    max(bill_activity.activity),
    bill_activity.bill_type,
    bill_activity.bill_amt,
    encounter.encounter_id
    from encounter (several joins later) JOIN bill_activity
    where bill_activity.bill_type in (blah, blah2, blah3)
    group by
    encounter.encounter_id,
    bill_activity.bill_type
    
    Basically, the most recent activity of each type, per encounter.
    But including other information (such as the activity amount) in the select, requires it to be put in the group by, which means that it then creates another line for each distinct amount when I need a single result per type x encounter.

    You could do a select on the MAX criteria for bill_activity within you join... assuming your unique identifier which you are joining is the encounter_id
    
    SELECT e.encounter_id, b.activity, b2.bill_activity.bill_type, b2.bill_activity.bill_amt
    FROM encounter e
    JOIN (SELECT MAX(bill_activity.activity), encounter_id FROM bill_activity GROUP BY encounter_id) b ON e.encounter_id = b.encounter_id
    JOIN bill_activity b2 ON b.activity = b2.activity
    

    At the very least, this might help get you on the right track.
    I attempted to do something along these lines, because I remembered that they want the total activity amount of each type (which could be the result of multiple activities) but only the date of the most recent activity of each type, so I was able to break out the aggregates and the group by into something that should have worked. The problem is, to get from encounter_ID to the bill_activity table takes 4-5 joins, and I have to look up around 8 different activity types for each encounter, that all need to be split out. I tried it a bunch of different ways with subqueries in the select, subqueries in the from, a materialized subquery which gets them all and then is joined using different criteria.
    All of those resulted in the query running for about 20 minutes before I canceled it and not even returning a partial result set.

    What I ended up doing is pulling all the qualifying encounters, demographic data, and the encounter balance totals that are stored directly in that encounter's entry in the encounter table on the financial side, instead of having to calculate them from the transaction log.
    Then all the other stuff is each its own separate query that I'm passing the list of qualifying encounters to, which is working so far. The problem with this approach, is because of the way this Business Objects product does it, there's an internal limit on the number of records that can be passed in a list from one query to another, so over a large enough timespan it will cause the report to fail. It's also cumbersome as fuck to set up on the report formatting side of it.

    On the other hand, the deadline on this is the 13th, and for the initial run it will work well enough, and I can translate it into the other reporting solution (which takes longer to move into the Prod environment due to restrictions from our corporate overlords) before it gets big enough to cause problems.

    steam_sig.png
  • GhotiGhoti Registered User regular
    So I am at a bit of a loss on whether or not you still would like further assistance with your query. There are probably some refinements which could help with your performance. It also sounds like your design would have the activity type as a input variable. You would have to run it several times, but it should reduce the set of information to the point where it does not fail.

    What you described your join select would look more like this:
    JOIN (SELECT MAX(bill_activity.activity) AS activity, SUM(bill_activity.bill_amt) AS amt, bill_activity.bill_type, {joinparameter} FROM bill_activity GROUP BY bill_activity.bill_type, {joinparameter})
    

    This would give you the latest activity date, sum up the amount by type and allow you to make your joins back to the encounter_id.

  • TofystedethTofystedeth Registered User regular
    Nah, I'm pretty much done with it by now. That's pretty much exactly what I did, and it didn't work. There's a lot of limitations in the reporting systems we have to use for our EMR that we have to work around.
    So much stuff would be easier if we were allowed to make views, or functions, or custom tables, but we can't.

    steam_sig.png
  • bowenbowen How you doin'? Registered User regular
    Nah, I'm pretty much done with it by now. That's pretty much exactly what I did, and it didn't work. There's a lot of limitations in the reporting systems we have to use for our EMR that we have to work around.
    So much stuff would be easier if we were allowed to make views, or functions, or custom tables, but we can't.

    bwah?

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • InfidelInfidel Heretic Registered User regular
    bowen wrote: »
    Nah, I'm pretty much done with it by now. That's pretty much exactly what I did, and it didn't work. There's a lot of limitations in the reporting systems we have to use for our EMR that we have to work around.
    So much stuff would be easier if we were allowed to make views, or functions, or custom tables, but we can't.

    bwah?

    Health industry where you have COTS applications and all the integration/reporting is done custom, but you have to deal with all your vendors and are super limited to what you can and cannot do with their database.

    At least that's what it was for me!

    OrokosPA.png
  • bowenbowen How you doin'? Registered User regular
    Oh I assumed he was writing an EMR not using an OTS app.

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • GhotiGhoti Registered User regular
    Ah. I've been on the wrong end of Business Objects permissions as well. If this is something you will be running on a consistent basis, maybe you can share all or some of what you have built already and see if a view could be created from its design.

  • TofystedethTofystedeth Registered User regular
    bowen wrote: »
    Oh I assumed he was writing an EMR not using an OTS app.
    Oh lord no. I'm not writing an EMR. We're a huge organization. That way lies madness.

    We use a product that rhymes with 'burner'.

    steam_sig.png
  • bowenbowen How you doin'? Registered User regular
    bowen wrote: »
    Oh I assumed he was writing an EMR not using an OTS app.
    Oh lord no. I'm not writing an EMR. We're a huge organization. That way lies madness.

    We use a product that rhymes with 'burner'.

    There's like hundreds of thousands of EMRs!

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • TofystedethTofystedeth Registered User regular
    edited May 2016
    hippofant wrote: »
    Ohhh shi, there is absolutely an SQL way to collapse that, but I forget what it was since I last had to use it some 10+ years ago.

    Edit: One way to do it is to select out the columns you want, and re-join. If you're using a GROUP BY, SQL demands that everything in the SELECT be in the GROUP BY or be inside an aggregate function, because otherwise, it doesn't know what value to return in the record. So you could take out bill_activity.bill_amt, use this as a subquery, and then join the results of the subquery with bill_amt to get the full resultset you want. I'm not sure how efficient that is though.

    I think I found it*, from this SO answer. Nest 2 queries. The first one gets all the columns you care about, plus an additional one that uses the Rank() analytic function to rank the records partitioned by, in this case encounter_id and activity_type_code, according to activity_date_time, then the outer query gets just the columns I want, where rank = 1.


    *Again. This is far from the first time I've had to do this sort of thing I keep forgetting how. I finally remembered the proper terms to get me back to this very same SO answer.

    Tofystedeth on
    steam_sig.png
  • gavindelgavindel The reason all your software is brokenRegistered User regular
    I sometimes wish I could talk more freely about all the most eventful stuff at work.

    But while I like sharing how to nuke things from orbit, I like having a job tomorrow more...

    Book - Royal road - Free! Seraphim === TTRPG - Wuxia - Free! Seln Alora
  • BigBadWolfBigBadWolf Grandma's HouseRegistered User regular
    hippofant wrote: »
    Ohhh shi, there is absolutely an SQL way to collapse that, but I forget what it was since I last had to use it some 10+ years ago.

    Edit: One way to do it is to select out the columns you want, and re-join. If you're using a GROUP BY, SQL demands that everything in the SELECT be in the GROUP BY or be inside an aggregate function, because otherwise, it doesn't know what value to return in the record. So you could take out bill_activity.bill_amt, use this as a subquery, and then join the results of the subquery with bill_amt to get the full resultset you want. I'm not sure how efficient that is though.

    I think I found it*, from this SO answer. Nest 2 queries. The first one gets all the columns you care about, plus an additional one that uses the Rank() analytic function to rank the records partitioned by, in this case encounter_id and activity_type_code, according to activity_date_time, then the outer query gets just the columns I want, where rank = 1.


    *Again. This is far from the first time I've had to do this sort of thing I keep forgetting how. I finally remembered the proper terms to get me back to this very same SO answer.

    If you're using SQL Server as a back-end, you should be able to do this fairly simply using CROSS APPLY. Using the train example from the SO answer it would be something like:
    SELECT trains.Train, LastTrain.Dest, LastTrain.MaxTime
    FROM <uniqueListOfTrains> as trains
    CROSS APPLY
    (
    	SELECT TOP 1 Train, Dest, Time 
    	FROM TrainTable
    	WHERE TrainTable.Train = trains.Train
    	Order By Time DESC
    ) as LastTrain
    

    The results in the cross apply are calculated for each row in your outer query (filtered by the where clause) - so you can return as many records as you like for each record in the outer query. Performance wise I've always found it much faster than nested joins, and a lot easier to understand!

    If you're not using SQL server than rank() functions are probably your best bet.

  • TofystedethTofystedeth Registered User regular
    edited May 2016
    Yup. This is using Oracle.
    Though I will try and remember that bit for later, because all of our internal stuff that we do ourselves is SQL Server.

    Tofystedeth on
    steam_sig.png
  • Bendery It Like BeckhamBendery It Like Beckham Hopeless Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

  • SpawnbrokerSpawnbroker Registered User regular
    DevOps has always seemed to me like a title that was invented by companies who want to pay one person to do three jobs.

    Steam: Spawnbroker
  • bowenbowen How you doin'? Registered User regular
    edited May 2016
    DevOps has always seemed to me like a title that was invented by companies who want to pay one person to do three jobs.

    it was

    I do it

    it's the only way I can get paid a livable wage, without having to invest 10k to move across the country

    bowen on
    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • TofystedethTofystedeth Registered User regular
    Yesss, gettin' a new laptop at work to replace my ancient desktop. Normally I'd stick with desktop, but because we pay Dell per device, they're trying to get us all down to one device. My current desktop is probably about 8 years old, and has only 4GB RAM.
    This way I have something I can take to meetings. Currently I have a "training" laptop that was going unused. It won't even turn on unless plugged in.

    steam_sig.png
  • mightyjongyomightyjongyo Sour Crrm East Bay, CaliforniaRegistered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

    Do you know what a linux is? Is source control not a foreign concept? If so then that puts you ahead of most people.
    bowen wrote: »
    DevOps has always seemed to me like a title that was invented by companies who want to pay one person to do three jobs.

    it was

    I do it

    it's the only way I can get paid a livable wage, without having to invest 10k to move across the country

    Yea. It's much cheaper to have me do three jobs for additional pay than invest in three people. Until I get burned out and quit, anyway.

  • bowenbowen How you doin'? Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

    Do you know what a linux is? Is source control not a foreign concept? If so then that puts you ahead of most people.

    That's pretty pathetic, yet, so hard to find a job that pays well still :(

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • mightyjongyomightyjongyo Sour Crrm East Bay, CaliforniaRegistered User regular
    We had a training on DevOps recently, it was pretty much coding for sysadmins. Not trying to disparage sysadmins, but it was touting "infrastracture as code" as if code were a foreign concept.

  • zeenyzeeny Registered User regular
    edited May 2016
    Some of the most fun stuff I've done in the past 6 months has been DevOps and I've had a chance to work on a lot of fun things.
    I love the concept of you build it, you own it.

    zeeny on
  • KakodaimonosKakodaimonos Code fondler Helping the 1% get richerRegistered User regular
    Devops really takes off if you can get it integrated into the coding side. When we had close to 200 projects in the build we finally convinced everyone to break it up into sub projects, slap them into a CI solution and an internal NuGet repository and bam, builds don't take forever.

  • mightyjongyomightyjongyo Sour Crrm East Bay, CaliforniaRegistered User regular
    Devops really takes off if you can get it integrated into the coding side. When we had close to 200 projects in the build we finally convinced everyone to break it up into sub projects, slap them into a CI solution and an internal NuGet repository and bam, builds don't take forever.

    That was our motivation as well. The problem is that when we split into sub projects, the devops part was half-assed so now it's creaking and falling apart. I was the only one stupid enough to volunteer to fix it.

  • mightyjongyomightyjongyo Sour Crrm East Bay, CaliforniaRegistered User regular
    But yea, overall I like devops. Theres a ton of cool stuff to leverage and different ways to implement it. Unfortunately no one sees it as a full-time job.

  • InfidelInfidel Heretic Registered User regular
    DevOps isn't a title but a practice/department?

    I mean, you can have only one person doing it all, but that's nothing new in IT.

    I'm structuring my company with "DevOps" because I want all staff involved with the build and maintenance of our platform collaborating much more than conventional. We are doing a web platform for a highly regulated industry, we need a solid deployment and management.

    Right now still doing it myself but I'm looking for someone on the server admin side. Adding a new dev in a week and looking to add two more soon, starting to get a nice little team here...

    OrokosPA.png
  • PhyphorPhyphor Building Planet Busters Tasting FruitRegistered User regular
    edited May 2016
    I have done a thing which I am calling Hello World, The Hard Way wherein I attempt to build a self-hosted operating system and hoping to avoid the analysis paralysis I have run into before because if I write about it I can't (as easily) sit on my ass debating driver interface specifications, IPC schemas and doman specific languages with myself forever and making zero progress

    I think a few people in here mentioned interest in something like this a while back

    Phyphor on
  • Bendery It Like BeckhamBendery It Like Beckham Hopeless Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

    Do you know what a linux is? Is source control not a foreign concept? If so then that puts you ahead of most people.

    I do know Linux, and source control isn't entirely foreign.

    I also know enough Ruby to write Puppet scripts.


  • zeenyzeeny Registered User regular
    Man, I honestly can't tell if that last line is sarcasm or serious.

  • Bendery It Like BeckhamBendery It Like Beckham Hopeless Registered User regular
    Man, I honestly can't tell if you're condescending or not.

This discussion has been closed.