As mentioned in unutbu’s comment, groupby’s filter is the equivalent of SQL’S HAVING: In [11]: df = pd.DataFrame([[1, 2], [1, 3], [5, 6]], columns=[‘A’, ‘B’]) In [12]: df Out[12]: A B 0 1 2 1 1 3 2 5 6 In [13]: g = df.groupby(‘A’) # GROUP BY A In [14]: g.filter(lambda x: len(x) > … Read more
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html Basically you declare error handler which will call rollback START TRANSACTION; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; EXIT PROCEDURE; END; COMMIT;
Yes! Here you have another example: UPDATE prices SET final_price= CASE WHEN currency=1 THEN 0.81*final_price ELSE final_price END This works because MySQL doesn’t update the row, if there is no change, as mentioned in docs: If you set a column to the value it currently has, MySQL notices this and does not update it.
You need to aggregate the data first, this can be done using the GROUP BY clause: SELECT Group, COUNT(*) FROM table GROUP BY Group ORDER BY COUNT(*) DESC The DESC keyword allows you to show the highest count first, ORDER BY by default orders in ascending order which would show the lowest count first.
SHOW CREATE PROCEDURE <name> Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. Swap PROCEDURE for FUNCTION for a stored function.
SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more
Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation: SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);
Use unnest. For example: CREATE OR REPLACE FUNCTION test( p_test text[] ) RETURNS void AS $BODY$ BEGIN SELECT id FROM unnest( p_test ) AS id; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 1;
The command for listing all triggers is: show triggers; or you can access the INFORMATION_SCHEMA table directly by: select trigger_schema, trigger_name, action_statement from information_schema.triggers You can do this from version 5.0.10 onwards. More information about the TRIGGERS table is here.
Well, I believe it’s an “Old but Gold” question. The answer is: “It depends!”. The performances are such a delicate subject that it would be too much silly to say: “Never use subqueries, always join”. In the following links, you’ll find some basic best practices that I have found to be very helpful: Optimizing Subqueries … Read more