SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

# ? Oct 15, 2017 18:34

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 12:00

LongSack: Jan 17, 2003

Nolgthorn posted:

It feels like the gender and the reciprocal relationship are separate things. The gender is just one attribute of an unlimited number of possible attributes which could be assigned to a character.

snip

That way I can have as many relationships between characters of any type that I want. As well as many character attributes as I want as well. This is starting to get opinionated but that's how I'd do it. If there were a common set of attributes or relationships, I'd keep them in a separate table so they could be perhaps "selected" easily in the ui.

The reason for the gender in the reciprocal relationship table is so that i can do things like:

<Aunt>,<Female>,<Niece>
<Aunt>,<Male>,<Nephew>

The Common set of attributes are actually columns in the Character table, the Custom Property table is for other properties.

The reason I was trying to use IDs and Foreign Keys in all of this was for Referential Integrity, but if I abandon that and just use the description fields instead of IDs I think my problems go away or are at least minimized. I can provide a "clean-up" function where descriptions that are no longer defined in the appropriate tables can be removed from characters (or retained, doesn't really matter at that point).

Thanks for the response, it gives me some options and things to think about.

# ? Oct 15, 2017 18:55

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

The six permanently unread posts on this thread reminds me: ZDR since you've posted here in the past can you share what went wrong with your query?

# ? Oct 15, 2017 19:52

Nolgthorn: Jan 30, 2001; The pendulum of the mind alternates between sense and nonsense

I had to post my response (first comment on this page) two times, the first time it said my post was successful and then it said the page it was trying to send me to didn't exist. If I backed out of the thread I was the most recent poster. But the page didn't exist.

I had to post again. That got my post to appear and the page to exist.

# ? Oct 16, 2017 01:23

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Nolgthorn posted:

I had to post my response (first comment on this page) two times, the first time it said my post was successful and then it said the page it was trying to send me to didn't exist. If I backed out of the thread I was the most recent poster. But the page didn't exist.

I had to post again. That got my post to appear and the page to exist.

Many threads are broken like this. The only known fix at this time is recreate the thread.

# ? Oct 16, 2017 02:20

Nolgthorn: Jan 30, 2001; The pendulum of the mind alternates between sense and nonsense

This is so meta

# ? Oct 16, 2017 03:02

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I bet it was because of me posting in between ZDR so now if I post I'll fix it.

# ? Oct 16, 2017 22:00

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Methinks not. Just now it said six new, yet there were none.

Just get some baby goon to sign an NDA, give em a code dump, and let them track down the issue.

# ? Oct 18, 2017 00:09

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

I'm pretty sure the problem is that the "number of posts in the thread" is materialized in the thread record, and this is incremented every time someone posts. Meanwhile, the unread post count probably looks directly at the post records, so now that 6 posts have been deleted from the post table, the number of read posts is always at least 6 less than the "number of posts" on the thread. So either zdr can fix all threads where the post count doesn't line up, or we can just deal with it and/or recreate the thread.

# ? Oct 18, 2017 18:01

kiwid: Sep 30, 2013

# ? Oct 23, 2017 19:24

Kuule hain nussivan: Nov 27, 2008

kiwid posted:

With MySQL, is it possible to group by just certain groups and have everything else grouped as "Other"?

Example: I just want group 1 and 2 on their own and everything else grouped together.

Table:
group | quantity
--------------------
group1 | 5
group1 | 2
group3 | 4
group2 | 1
group1 | 6
group3 | 8
group4 | 5
group5 | 1

I want a result set like this where I sum the quantities:
group1 | 13
group2 | 1
other | 18

Possible with one query?

You could use a case in your initial select to lump the additional groups into other and then group by those values. Not sure if you can do it in one query, but it'll definitely work with a subquery.

# ? Oct 23, 2017 19:47

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Kuule hain nussivan posted:

You could use a case in your initial select to lump the additional groups into other and then group by those values. Not sure if you can do it in one query, but it'll definitely work with a subquery.

It can probably be done directly in line.

code:

SELECT 
    CASE 
        WHEN (foo = 'group1' OR foo = 'group2') THEN foo 
        ELSE 'Other' 
    END AS foo, 
    SUM(quantity) AS quantity
FROM bar 
GROUP BY foo DESC

Oh also we should start a new thread because according to all the reports in QCS none of the broken threads are getting repaired, and new posts in this thread will be unreadable once it adds a new page.

# ? Oct 23, 2017 20:00

BabyFur Denny: Mar 18, 2003

McGlockenshire posted:

Oh also we should start a new thread because according to all the reports in QCS none of the broken threads are getting repaired, and new posts in this thread will be unreadable once it adds a new page.

Sorry, all the replies to your question are on the unreadable new page.

# ? Oct 23, 2017 20:44

Kuule hain nussivan: Nov 27, 2008

McGlockenshire posted:

It can probably be done directly in line.
code:
SELECT 
    CASE 
        WHEN (foo = 'group1' OR foo = 'group2') THEN foo 
        ELSE 'Other' 
    END AS foo, 
    SUM(quantity) AS quantity
FROM bar 
GROUP BY foo DESC
Oh also we should start a new thread because according to all the reports in QCS none of the broken threads are getting repaired, and new posts in this thread will be unreadable once it adds a new page.

Yeah, I wasn't sure if MySQL would allow the alias for the groups to be used in the group by statement of the same query that creates the alias, but that's cool and good.

Edit: Thread seems to have fixed itself!

# ? Oct 23, 2017 21:14

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

Kuule hain nussivan posted:

Edit: Thread seems to have fixed itself!

Please do not troll the SQL thread.

# ? Oct 23, 2017 21:38

Kuule hain nussivan: Nov 27, 2008

anthonypants posted:

Please do not troll the SQL thread.

It looked fine while I was the most recent poster

# ? Oct 24, 2017 07:56

wolrah: May 8, 2006; what?

Kuule hain nussivan posted:

It looked fine while I was the most recent poster

Testing this theory. It shows six unread for me right now, none unread within the normal thread view, but the last six posts (which I've already seen a few dozen times) are showing as unread on the "post reply" page.

edit: Nope, still the same behavior.

# ? Oct 24, 2017 14:34

Kuule hain nussivan: Nov 27, 2008

wolrah posted:

Testing this theory. It shows six unread for me right now, none unread within the normal thread view, but the last six posts (which I've already seen a few dozen times) are showing as unread on the "post reply" page.

edit: Nope, still the same behavior.

It seemed to be a feature of the Awful app. When I post, it marks the thread as having no unread posts, but this changes back to 6 unread as soon as I refresh my bookmarks-/subforum-view. In short, still hosed.

# ? Oct 24, 2017 14:44

wolrah: May 8, 2006; what?

Kuule hain nussivan posted:

It seemed to be a feature of the Awful app. When I post, it marks the thread as having no unread posts, but this changes back to 6 unread as soon as I refresh my bookmarks-/subforum-view. In short, still hosed.

Makes sense. I think the weird behavior I see on the post page is an artifact of the SALR Chrome extension and not actually something the forums are directly doing (though it's still obviously getting triggered by the forum bug).

# ? Oct 24, 2017 15:10

anthonypants: May 6, 2007; by Nyc_Tattoo; Dinosaur Gum

We've got some reports running off an Oracle 10g(?) database on an RHEL 5 server, and the reporting server is running SQL Server 2005 on Server 2003, and I'm looking at the MSSQL Migration Assistant, and it looks really nice. Except for the estimated 60-day conversion time, that's not so great.

# ? Oct 24, 2017 19:58

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

T-SQL question:
We have an application that creates stored procedures that our customers write to save for later on our reporting DB.
Something I noticed tonight when investigating problems with this system that has not been touched in yonks is a delay between the CREATE PROCEDURE command being issued and the sproc actually showing up as a SQL object.

I clocked this delay in one case as being between 10 and 12 minutes. Interestingly, when the SQL object showed up in sys.procedures, the create_date and modified_date showed a timestamp of right around the time the CREATE PROCEDURE command was issued. Has anyone seen this behavior before?

# ? Oct 26, 2017 02:10

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Nth Doctor posted:

T-SQL question:
We have an application that creates stored procedures that our customers write to save for later on our reporting DB.
Something I noticed tonight when investigating problems with this system that has not been touched in yonks is a delay between the CREATE PROCEDURE command being issued and the sproc actually showing up as a SQL object.

I clocked this delay in one case as being between 10 and 12 minutes. Interestingly, when the SQL object showed up in sys.procedures, the create_date and modified_date showed a timestamp of right around the time the CREATE PROCEDURE command was issued. Has anyone seen this behavior before?

Mystery solved: it's a red herring.
My role on the server wasn't being granted any permissions on the new sproc at creation time, so I couldn't even know it existed. A job came around and granted me the permissions a while later.

# ? Oct 26, 2017 03:33

Nolgthorn: Jan 30, 2001; The pendulum of the mind alternates between sense and nonsense

I'd like to keep a record of the first time a string occurred, plus the number of times it occurred.

code:

my_strings
====
id: String
occurrences: Number
created_at: Date

My question is about how to insert/update these records. The first insert should default to 1 occurrence, but subsequent occurrences should increment the row. Is there a way to do that in a single operation?

# ? Oct 26, 2017 12:31

Nolgthorn: Jan 30, 2001; The pendulum of the mind alternates between sense and nonsense

This is actually built into Mysql almost exactly what I needed.

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

code:

INSERT INTO my_strings (id)
VALUES ("MyString")
ON DUPLICATE KEY
UPDATE occurrences=occurrences+1;

How about postgresql?

# ? Oct 26, 2017 12:50

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

Nolgthorn posted:

This is actually built into Mysql almost exactly what I needed.

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
code:
INSERT INTO my_strings (id)
VALUES ("MyString")
ON DUPLICATE KEY
UPDATE occurrences=occurrences+1;
How about postgresql?

Looks like postgress uses ON CONFLICT https://www.postgresql.org/docs/9.5/static/sql-insert.html

# ? Oct 26, 2017 14:02

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Is there a good resource for "Here's a list of PostgreSQL extensions that are actually solid and useful and which you could consider using in production"?

# ? Oct 26, 2017 16:16

Withnail: Feb 11, 2004

Can anyone help a sql dummy how to get the following hierarchical query to order by the hierarchy? The query below just orders by the order in the table.

WITH PartList AS
(
SELECT *
FROM dbo.Part
WHERE PartId = '42'
UNION ALL
SELECT a.*
FROM dbo.Part a
INNER JOIN PartList p ON a.ParentPartId = p.PartId
)
SELECT * FROM PartList

# ? Oct 27, 2017 01:33

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Withnail posted:

Can anyone help a sql dummy how to get the following hierarchical query to order by the hierarchy? The query below just orders by the order in the table.

WITH PartList AS
(
SELECT *
FROM dbo.Part
WHERE PartId = '42'
UNION ALL
SELECT a.*
FROM dbo.Part a
INNER JOIN PartList p ON a.ParentPartId = p.PartId
)
SELECT * FROM PartList

It looks like you want a full hierarchy tree starting with the root at PartId 42, correct?

Recursive CTE like you have is what you'll want but you should add a level to the query like this:

code:

WITH PartList AS
( 
  SELECT
    P.*,
    0 as Layer
  FROM dbo.Part AS P
  WHERE PartId = '42'
  UNION ALL
  SELECT
    a.*,
    p.Layer+1 as Layer
  FROM dbo.Part AS a 
  INNER JOIN PartList AS p
    ON a.ParentPartId = p.PartId 
) 
SELECT * FROM PartList AS P
ORDER BY P.Layer;

# ? Oct 27, 2017 01:49

Withnail: Feb 11, 2004

I want it expanded like below. Your example doesn't seem to be doing this.

I'm used to using oracle's 'connect by' to do this...

# ? Oct 27, 2017 02:02

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Withnail posted:

Can anyone help a sql dummy how to get the following hierarchical query to order by the hierarchy? The query below just orders by the order in the table.

code:

WITH PartList AS
( 
  SELECT *,
         part_name as hier
    FROM dbo.Part
   WHERE PartId = '42'
  UNION ALL
  SELECT a.*,
         p.hier+'|'+a.part_name
    FROM dbo.Part a 
         INNER JOIN PartList p ON a.ParentPartId = p.PartId 
) 
SELECT * FROM PartList
order by hier

# ? Oct 27, 2017 15:11

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Jethro posted:

code:

WITH PartList AS
( 
  SELECT *,
         part_name as hier
    FROM dbo.Part
   WHERE PartId = '42'
  UNION ALL
  SELECT a.*,
         p.hier+'|'+a.part_name
    FROM dbo.Part a 
         INNER JOIN PartList p ON a.ParentPartId = p.PartId 
) 
SELECT * FROM PartList
order by hier

Cool approach. I like it.

# ? Oct 27, 2017 15:22

BabyFur Denny: Mar 18, 2003

Woah did this thread just fix itself?

# ? Oct 27, 2017 19:19

Portland Sucks: Dec 21, 2004; à¼¼ ã¤ â_â à¼½ã¤

What's a good resource for transitioning out of the "I can write queries and join tables" phase. I know that procedures and parameters and whatnot are a thing, but I always fall back to just doing all my data manipulation in python. I'd like to be able to take more advantage of the DB engine, but outside of "a table is a thing with columns where you put stuff" I've never really had an opportunity to learn how they should be used.

# ? Oct 27, 2017 19:25

skrath: Nov 14, 2000; Horsum venit vir qui fert locustas!

Trying to figure out how to work out how get a combination of rows with MySQL. I actually have no idea where to start with this.

My goal is to get a count of how many times a game (the GameID) was the same for each/any combination of PlayerIDs, so I can figure out how many total times a combination of players has occured. I need to do this with every single combination of players (if possible).

PlayerLog table:

code:

RecordID | GameID | PlayerID | othercolumns..

1        | 1001   | 55       | .. 
2        | 1001   | 72       | .. 
3        | 1001   | 82       | .. 
4        | 1002   | 55       | .. 
5        | 1002   | 72       | .. 
6        | 1002   | 82       | .. 
7        | 1002   | 90       | ..

In this example..
Player1ID(55) + Player2ID(72) = 2 games
Player2ID(72) + Player3ID(82) = 2 games
Player1ID(55) + Player4ID(90) = 1 game
Player1ID(55) + Player2ID(72) + Player4ID(90) = 1 game
etc

Any suggestions?

# ? Oct 29, 2017 04:45

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Something like the following should get you the counts for maximal descriptions of the players in a game. There might be a faster or more elegant way; I haven't thought about it at length. And I haven't tested this, so it might need some adjusting.

When I say maximal, I mean that if a game has players 1, 2 and 3 in it then it will be counted for '1,2,3' but not for '1,2' or '1,3' or '2,3'.

I think you are better off extracting all the data and generating the output you describe using your favourite scripting language, rather than SQL. If there were a maximum number of players that could be in any given game, and if that maximum number were small, it might make it easier to do in SQL.

code:

SELECT
    Players,
    COUNT(*) AS NumGames
FROM
    (
        SELECT
            GROUP_CONCAT(PlayerID ORDER BY PlayerID SEPARATOR ',') AS Players
        FROM
            PlayerLog
        GROUP BY
            GameID
    ) AS subq
GROUP BY
    Players

# ? Oct 29, 2017 23:45

Love Stole the Day: Nov 4, 2012; Please give me free quality professional advice so I can be a baby about it and insult you

skrath posted:

Trying to figure out how to work out how get a combination of rows with MySQL. I actually have no idea where to start with this.

My goal is to get a count of how many times a game (the GameID) was the same for each/any combination of PlayerIDs, so I can figure out how many total times a combination of players has occured. I need to do this with every single combination of players (if possible).

PlayerLog table:
code:
RecordID | GameID | PlayerID | othercolumns..

1        | 1001   | 55       | .. 
2        | 1001   | 72       | .. 
3        | 1001   | 82       | .. 
4        | 1002   | 55       | .. 
5        | 1002   | 72       | .. 
6        | 1002   | 82       | .. 
7        | 1002   | 90       | .. 
In this example..
Player1ID(55) + Player2ID(72) = 2 games
Player2ID(72) + Player3ID(82) = 2 games
Player1ID(55) + Player4ID(90) = 1 game
Player1ID(55) + Player2ID(72) + Player4ID(90) = 1 game
etc

Any suggestions?

I spent some time looking into this because I'm new to SQL and thought this would be good practice. I didn't find exactly the answer, but I'm pretty sure I found what comes one step right before the answer you're looking for.

Here is what you can do to get all possible combinations of any two given PlayerIDs:

code:

postgres=# WITH P1 AS (
postgres(#      SELECT DISTINCT PlayerID
postgres(#      FROM PlayerLog
postgres(#      ),
postgres-#      P2 AS (
postgres(#      SELECT DISTINCT PlayerID
postgres(#      FROM PlayerLog
postgres(#      )
postgres-# SELECT *
postgres-# FROM P1, P2
postgres-# WHERE P1.PlayerID != P2.PlayerID
postgres-# ORDER BY P1.PlayerID ASC;
 playerid | playerid
----------+----------
        1 |        2
        1 |        3
        2 |        3
        2 |        1
        3 |        2
        3 |        1
(6 rows)

With this table of all distinct playerID combinations, you can feed this into a recursive function using something like this:

code:

WITH RECURSIVE get_people_who_played_with(P1) AS (
	-- select one player id and game id from player log
	INTERSECT
	-- select all of the others except that one?
)
SELECT * FROM blah;

The documentation (I was using PostgreSQL rather than MySQL like you asked because that's the only one I am able to work with as I learn more about this) is here: https://www.postgresql.org/docs/10/static/queries-with.html

I wasn't able to figure out how to implement that function, but I'm pretty sure that you could figure it out if you invest some time into it. I ran out of steam/interest, sadly.

Maybe it's actually easier than that somehow. Maybe it could be possible to just add some kind of HAVING clause to the original thing and not need this RECURSIVE deal at all. I'm not experienced enough yet.

edit-- Also before I close this Notepad window, here is how you can explicitly get the matching GameID for any two given PlayerIDs:

code:

WITH Player_X_And_Y_Together AS (
	SELECT GameID 
	FROM PlayerLog
	WHERE PlayerID=1

	INTERSECT

	Select GameID
	FROM PlayerLog
	WHERE PlayerID=2
	)
SELECT * 
FROM Player_X_And_Y_Together;

I'm sure it's possible to expand this out to any number of given IDs, but I haven't yet learned how.

Love Stole the Day fucked around with this message at 14:21 on Oct 30, 2017

# ? Oct 30, 2017 14:11

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Depending on the maximum number of players that can be in a game and the size of the userbase, neither of which we know, that approach might be impractical as it is O(n^p) where n is the number of users and p is the max players in a game.

# ? Oct 30, 2017 15:58

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

You'll want a recursive cte (so at least MySQL 8 or a better database) because your example target is"all combinations at least...". That is you count two players in a game even if there was a third player in your example.

Ergo start with all games played by each player, then searching within that for a second player, then within those for the third, and so on. You'll need to track a few things to help the query along. Left as exercise for the reader.

Soooo homework or hackerrank?

# ? Oct 30, 2017 23:27

skrath: Nov 14, 2000; Horsum venit vir qui fert locustas!

Hammerite posted:

Something like the following should get you the counts for maximal descriptions of the players in a game. There might be a faster or more elegant way; I haven't thought about it at length. And I haven't tested this, so it might need some adjusting.

When I say maximal, I mean that if a game has players 1, 2 and 3 in it then it will be counted for '1,2,3' but not for '1,2' or '1,3' or '2,3'.

This is pretty much spot on, thank you! I tweaked it a little but I think I'm going to have to run it through a PHP script/loop to get the exact results I want (which is every combination of PlayerIDs) - I can do it by altering the GROUP BY and adding a WHERE clause between X PlayerIDs in the subquery, and making sure that it groups by team (instead of just GameID, as there are two teams playing in a game).

Hammerite posted:

Depending on the maximum number of players that can be in a game and the size of the userbase, neither of which we know, that approach might be impractical as it is O(n^p) where n is the number of users and p is the max players in a game.

The PlayerLog table is 300,000 rows, so was hoping to do it all in SQL if possible. Max players per team per game is 15 on the roster.

PhantomOfTheCopier posted:

You'll want a recursive cte (so at least MySQL 8 or a better database) because your example target is"all combinations at least...". That is you count two players in a game even if there was a third player in your example.

Ergo start with all games played by each player, then searching within that for a second player, then within those for the third, and so on. You'll need to track a few things to help the query along. Left as exercise for the reader.

Soooo homework or hackerrank?

Neither -- working on a new algorithm/formula for team cohesion in my online basketball simulation. I wanted to take into account all times a player has played with any other player, as well as the whole group, to work out an algorithm to see how together the team is (before any 'chemistry' influences).

Thanks everyone for your help; I'm pretty much a beginner at SQL and just hack my way using poorly thought out programming loops to get the data I want. Trying to do it all in queries is a skill I'm still trying to learn.

skrath fucked around with this message at 00:29 on Oct 31, 2017

# ? Oct 31, 2017 00:13

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 12:00

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

I kinda have to wonder what fifteen cross joins would do. :clint:

It might be more efficient to group on games, construct combinations from that, then go back and count repeats between games.

# ? Oct 31, 2017 23:58

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »